Row Versioning in SQL Server 2005

SQL Server 2005 has introduced two new Isolation Levels. We can use these Isolation Levels for row versioning.

1. READ_COMMITTED_SNAPSHOT (statement level)
2. ALLOW_SNAPSHOT_ISOLATION (transaction level)

These Isolation level turned on database level. You can turn on the Isolation Level using below command.

ALTER DATABASE sqldbpool
SET READ_COMMITTED_SNAPSHOT ON

ALTER DATABASE sqldbpool
SET ALLOW_SNAPSHOT_ISOLATION ON


When above Isolation level is turned on it will enable the row versioning at database level. Transaction or statement views the data as it existed at the start of the statement or transaction, instead of protecting all reads with locks. Row versioning will reduce the blocking/deadlock issues and boost the database performance. Row versioning also prevents users from reading uncommitted data and prevents multiple users from attempting to change the same data at the same time.

You can query sys.databases to check the above isolation level status.

SELECT name, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases