Wednesday, March 28, 2012

Timestamp in SQL Server

It is a datatype which automatically generates a unique binary numbers. It is a versioning for rows in the table. Whenever any Insert / Update happend on the table this timestamp column increments a number. It doesn't store any date or time value.

A table can have only one timestamp column. The storage size is 8 bytes.

Both TimeStamp and Rowversion datatypes are similar only with name change. The Timestamp datatype is deprecated feature. It may be removed from future versions of SQL Server.

Syntax for creating a table with timestamp datatype

create table test1 (id int, timestamp) -- If you dont specify any column name it will creates a column with name "timestamp"

The timestamp is a autogenerated rowversion which will increments a value whenever new rows are inserted / updated If you import any table which has timestamp column the target table timestamps will be different from source. Because in source the rows may get updated and timestamp has some other value, whereas incase of target server it treats the rows are newly (first time) inserted rows and it creates a series of binary numbers

No comments: