Home > T-SQL > Timestamp Vs Rowversion

Timestamp Vs Rowversion


I have explained about timestamp in my previous blog post. But, this is the continuation of that blog post. Because,  timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms.

Timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server.

timestamp data type can be used without column name but rowversion must have column name while declaring in the table.

Example for timestamp column create in a table:

pic1

timestamp column will be generated automatically. You see the table design below.

pic2But, we cannot create rowversion column without column name and the DDL statement to create a table with row version is given below.

pic3We can perform same operation as we do with timestamp. Rowversion is an auto incremental column in a table and we cannot use more than one column with the type rowversion. It increments value when the insert and update query is executed for the table. It generates unique value with in the database. But, we should not use this column as key column in a table. Because, this column value will change when we update the row.

Example:

we are inserting few rows into the table and get the latest timestamp value of the database using @@dbts function.

pic3Now, I am going to update a row in this table.

Printing list of rows before update,

pic3After update, print the rows in the table to see the updated timestamp column.

pic3

We have learned how to use rowversion data type column in a table. Now, you can also use this data type in your database design. But, remember the limitations of rowversion.

Advertisements
Categories: T-SQL Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: