Is TimeStamp column unique?

问题:

Is TimeStamp unique in all rows?

Does it have a unique index on it?

回答1:

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

---

For more info, check THIS article.

回答2:

From the MSDN article, emphasis mine:

Timestamp is a data type that exposes automatically generated, unique binary numbers within a database. timestamp is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type.

It also provides this interesting note:

Duplicate timestamp values can be generated by using the SELECT INTO statement in which a timestamp column is in the SELECT list. We do not recommend using timestamp in this manner.

In SQL server 2008 and later, the timestamp type has been renamed to rowversion, presumably to better reflect its purpose and value.

回答3:

The docs says

unique binary numbers within a database

But in practice you can run into situations where this statement is not true. Since the value is generated from a database global counter you might run into problems when restoring databases and or using timestamp in tables in tempdb.

You can see the next value:

Select @@DBTS

Until SQL Server 2000 there was an undocumented command to change the value

DBCC CHECKDBTS (dbId, newTimestamp)

To change the timestamp in non ancient verswions of SQL server see this answer:

https://dba.stackexchange.com/a/11088/149485