Sybase的timestamp类型

转自http://action929.itpub.net/post/28194/306348
===========================================================作者: action929(http://action929.itpub.net)
发表于:2007.07.10 15:45
分类: Sybase
出处：http://action929.itpub.net/post/28194/306348
---------------------------------------------------------------今天有同事问我一个建表时候的错误：

1> create table aabbccddee( a timestamp,b timestamp)
2> go
Msg 2738, Level 16, State 2:
Server 'ipnet', Line 1:
A table can only have one timestamp column. Since table 'aabbccddee' already
has one, you can't add the column 'b'.

不能建2个时间类型？咋可能呢，上网查了一下

Sybase数据库中的timestamp为用户定义数据类型，它实际为varbinary(8)类型，但很多工程师把它当作date或time类型，这是错误的。其实在Sybase数据库中每一个数据库都会有一个全局timestamp，它被存放在dbtable内存结构中，它是一个顺序号，用于跟踪数据库中数据页的修改情况，主要被系统使用进行自身维护工作。用户不可以直接修改处理这种数据类型的数据，但使用DB-Library编程可以处理，特殊用户一般使用该数据类型来代替identity类型数据，需要注意的是，这个值只可以增大，不可以减小。

做了下测试：


1> create table test1(id int,times timestamp,times2 datetime)
2> go
1> insert into test1 values(1,null,getdate())
2> go
(1 row affected)
1> select * from test1
2> go
id times times2
----------- ------------------ --------------------------
1 0x0000000000faefd1 Jul 10 2007 3:43PM

(1 row affected)
1> insert into test1 values(2,0x11111,getdate())
2> go
Warning: A non-null value cannot be inserted into a TIMESTAMP column by the
user. The database timestamp value has been inserted into the TIMESTAMP field
instead.
(1 row affected)

1> insert into test1 values(2,getdate())
2> go
Warning: A non-null value cannot be inserted into a TIMESTAMP column by the
user. The database timestamp value has been inserted into the TIMESTAMP field
instead.
Msg 213, Level 16, State 4:
Server 'ipnet', Line 1:
Insert error: column name or number of supplied values does not match table
definition.





1> create table test11(id int,id2 int identity)
2> go
1> insert into test11 values (null,1)
2> go
Msg 7743, Level 16, State 1:
Server 'ipnet', Line 1:
An explicit value for the identity field in table 'test11' can only be specified

in an insert statement when a field list is used.
1> insert into test11 values (2,1)
2> go
Msg 7743, Level 16, State 1:
Server 'ipnet', Line 1:
An explicit value for the identity field in table 'test11' can only be specified

in an insert statement when a field list is used.
1> insert into test11 values (1)
2> go
(1 row affected)
1> select * from test11
2> go
id id2
----------- -----------
1 1

(1 row affected)

总结下： identity和timestamp用起来差不多，区别在于

(1) identity 的列，不能出现在insert语句中，而timestamp必须出现，而且必须为null。

(2)identity的值，是在表一级唯一，timestamp的值是在数据库一级唯一。

(3)identity的类型可以自己指定，timestamp不可以

自己在ASE 12环境下测试了:

drop table testing;
create table testing(c1 int, ts timestamp, c2 int);
insert into testing(c1, ts , c2) values(112,'2009-08-21',123);

数据可以insert成功,只是报警告,为null时正常:
Server Message:  Number  273, Severity  10
Server 'SYBAE', Line 1:
Warning: A non-null value cannot be inserted into a TIMESTAMP column by the user. The database timestamp value has been inserted into the TIMESTAMP field instead.

insert into testing(c1 , c2) values(102,113);
select * from testing;

查询结果:
c1          ts                 c2         
----------- --                 -----------
        112 0x0000000003078a06         123
        102 0x0000000003078a19         113