Tuesday, July 5, 2011

RowVersion Datatype

SQL Server 2008 introduce new data type is RowVersion. The value of RowVersion column automatically generate the value every insert or update occurred on the row. A table can have only one RowVersion column. This will be the replacement for timestamp data type. This is automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time. One of the best uses of this is for identifying data changes. 
The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

A column of type rowversion holds an internal sequence number that SQL Server automatically updates every time the row is modified.

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

The main purpose of RowVersion is for concurrency checking. In multi user environment, there is a possibility 2 users or more will update the same record in the same time.

If you are using LINQ to SQL then it is a good practice to include a timestamp / rowversion column for each entity table, as this allows LINQ to use optimistic concurrency very easily and results in slightly better performance for updates (as LINQ only has to query the timestamp column and does not have to compare other columns for changes).

No comments:

Post a Comment