What is row versioning in SQL Server?

When to use row versioning?

In SQL, row versioning is primarily used in the following cases:

• Optimistic concurrency control: Row versioning helps detect conflicts between concurrent transactions. When a transaction tries to modify a row, SQL Server compares the version number or timestamp of the row with the version number or timestamp of the transaction. If they don't match, the current transaction is rolled back.
• History of changes: Each time a row is modified, a new version is created with a timestamp and other metadata. The previous version is then stored in a separate temporal table. So, queries can be executed against the table as they existed at a specific time.
• Snapshot isolation: Transaction reads the data without any changes made by concurrent transactions. SQL Server copies the current version of the row to the tempDB each time it is modified. So, when a transaction reads the updated row, the version as it was when the transaction started is returned.

SQL Server introduces two transaction isolation levels that use row versioning:

• READ COMMITTED SNAPSHOT
• SNAPSHOT

READ COMMITTED SNAPSHOT isolation level

READ COMMITTED SNAPSHOT is a statement-based isolation level that uses row versioning to provide a snapshot of data taken as it was at the start of the statement. It means that the READ statement will not be able to see any changes made by other transactions until those changes have been committed.

In the case of updates, READ COMMITTED SNAPSHOT no longer uses row versions and switches to actual data. It identifies the rows to modify and then sets the update locks on these rows. The data stored in the latest committed snapshot taken before the READ statement has begun is returned.

To enable READ COMMITTED SNAPSHOT in SQL, you can use the SET READ_COMMITTED_SNAPSHOT ON clause in the ALTER DATABASE statement.

SNAPSHOT isolation level

SNAPSHOT is a transaction-based isolation level that uses row versioning to provide a snapshot of data taken as it was at the start of the transaction. Any transaction will read the committed values as they exist before the snapshot of the transaction is taken. If the original value has been modified, the other transaction will read this value after the snapshot transaction completes.

If the data has been updated, the snapshot transaction checks if these changes were made by a concurrent transaction committed after the transaction began. If so, an update conflict occurs, and the snapshot transaction is rolled back.

To enable SNAPSHOT in SQL, you can use the SET ALLOW_SNAPSHOT_ISOLATION ON clause in the ALTER DATABASE statement. Thus, which isolation level you should use depends on your specific needs.

Examples of how row versioning works

Now, let's have a look at some examples to see how row versioning works in SQL.

Example 1: Create a table with a rowversion column

In this example, we create the MyNewTable SQL table with three columns - ID, Name, and RowVersion - by executing the CREATE TABLE statement:

CREATE TABLE dbo.MyNewTable (
ID INT PRIMARY KEY,
Name NVARCHAR(50),
RowVersion ROWVERSION
);

The ID column has an integer data type and serves as a primary key in the table. The Name column is a string column that can hold up to 50 characters. Finally, the RowVersion column has a ROWVERSION data type, which will be automatically generated as a unique binary number of 8 bytes in size to each row in the table every time the row is modified or inserted.

Next, we execute the SELECT statement with the DBTS function to see the original rowversion value before updating the table. The function returns the current rowversion value for a database, which is 0x00000000000007D0.

SELECT @DBTS AS Before;

Example 2: Insert a row in a table with a rowversion column

Now, add a row to the table by executing the INSERT INTO statement:

INSERT INTO dbo.MyNewTable (ID, Name)
VALUES (1, 'Mike');

This will insert a row with the ID that equals 1, the name - Mike, and the new rowversion value which will be generated automatically. That's why we do not specify the value for the rowversion column.

Once done, execute the SELECT statement with the DBTS function to see the rowversion value after updates.

SELECT @DBTS AS After;

As you can see, the previous rowversion value 0x00000000000007D0 has been incremented to 0x00000000000007D1.

Example 3: Update a table with a rowversion column

Let's move on and modify the value for the row with the ID that equals 1 by adding 'Smith' to the Name column. For this, execute the UPDATE statement. Then, run the SELECT statement to see the rowversion value which is expected to be incremented during any table updates.

UPDATE dbo.MyNewTable
SET Name = 'Mike Smith'
WHERE ID = 1;
                            
SELECT * FROM dbo.MyNewTable;

In the output, we see that after updating the Name column, the rowversion value has been incremented to 0x00000000000007D2.

With the help of the rowversion column, you can identify whether the table row has been updated since the last time it was read. For example, if you execute the UPDATE statement against the row, the rowversion value will be incremented to. However, if the row is not modified, the rowversion value will remain unchanged since the previous time it was read.

Conclusion

To sum it up, row versioning is a powerful tool that can be used to improve the performance and concurrency of your database applications. To get the most benefit from row versioning, it is important to understand how it works. When to use this technique depends totally on your needs, whether you need to keep track of the row version for each row in the database or implement optimistic concurrency control. For row versioning as well as for other database-related tasks, you can easily use our ultimate tool - dbForge Studio for SQL Server which offers a bunch of comprehensive features and capabilities.