今天有同事问我一个建表时候的错误:
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不可以。
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/133835/viewspace-925766/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/133835/viewspace-925766/