pb on create table

Question

pb on create table

bob20000

SSC Veteran

Points: 228
More actions
November 28, 2002 at 2:17 am

#79796

i would like to create a table with two timestamp and i can't create why?
and how i can resolve this problem

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

This is a SQL Server restriction. A table can have only one Timestamp column.

If you need more than one timestamp, then you will have to consider partitioning the table into two tables, and have a timestamp in each table.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Why would you want two timestamps anyway? What is the point you are trying to accomplish as there may be a solution you missed.

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 3

Timestamp is not really an indicator of time in SQL Server. If you want 2 datetime fields, use that instead.

Timestamp is a database-wide unique number.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Going back to this, this is my first reaso for concern with using a SQL Timestamp type.

quote:
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.
A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

And as mentions the reason for one is by design.

quote:
A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.