In this post, we want to discuss an efficient techniques to learn or use Rowversion in SQL Server. Before we get started if you want to know about string case conversion to Title or Proper Case, please go through the following article: Function to Convert Text String to Title Case – Proper Case.

In SQL Server, rowversion is a data type that exposes automatically generated, unique binary numbers within a database. It allows you to version-stamp table rows with a unique value. This helps maintain the integrity of the database when multiple users are updating rows at the same time.

Each SQL Server database has a counter that is incremented for each insert or update operation that is performed on a table that contains a column with the rowversion data type (or its timestamp synonym, which is flagged for deprecation).

If a table contains a rowversion (or timestamp) column, any time a row is inserted or updated, the value of the rowversion column is set to the current rowversion value. This is true, even when an UPDATE statement doesn’t result in any changes to the data.

Example 1 – Create a Table with a rowversion Column

Here’s an example of creating a table with a rowversion column.

Result:

Take a look at the “Before” and “After” values in the results. This represents the current rowversion value. In this case, I created a new database and table, and the rowversion value started at 0x00000000000007D0. Once I inserted a row, the rowversion was incremented to 0x00000000000007D1.

Example 2 – Updates

As mentioned, the rowversion value is also incremented when you do an update. Example:

Result:

Here, I update the PetName column, and the VersionStamp column (rowversion) is incremented to 0x00000000000007D2.

Example 3 – Updates with No Changes

One of the cool things about rowversion is that it is incremented on all UPDATE operations even when no change takes place. For example, if I run the previous code again, the value of the VersionStamp column still changes, even though no change took place:

Result:

The value of the PetId and PetName columns didn’t change, but the VersionStamp column was incremented.

Example 4 – The timestamp Data Type

The timestamp data type is a synonym for rowversion. However, timestamp is one of the data types that have been flagged for deprecation in a future version of SQL Server. Unsurprisingly, Microsoft recommends avoiding using this feature in new development work and planning to modify applications that currently use this feature.

Therefore, if you encounter a database that uses the timestamp data type, you might want to think about changing it to rowversion. Just for demonstration purposes, here’s a modification of the first example to use timestamp instead of rowversion:

Result:

And while we’re at it, here’s the next example modified for timestamp:

Result:

You might have noticed that I didn’t actually provide a name for the timestamp column. If you don’t specify a column name, the SQL Server Database Engine generates the timestamp column name.

However, rowversion doesn’t allow this syntax, so you’ll need to specify the column name when using rowversion.

What is rowversion in SQL Server?

The article was published on January 6, 2020 @ 12:52 PM

Leave a Comment