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

Advertisements

2 thoughts on “Row Versioning in SQL Server 2005

Thanks for the comment, will get back to you soon... Jugal Shah

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