Problem solve Get help with specific problems with your technologies, process and projects.

Row versioning with SNAPSHOT isolation level

Using "dirty" reads is a thing of the past. SQL Server 2005 expert Adam Machanic discusses SQL Server 2005's new snapshot feature.

What is "row versioning" and how is it used in SQL Server 2005?

"Row versioning" refers to the process of storing previous versions of rows in the tempdb database. The stored versions can be read from transactions that began before the row was updated. This is the basis for SQL Server 2005's new SNAPSHOT isolation level, which provides a highly concurrent, non-blocking behavior but guarantees that readers will see only consistent and committed data.

Many SQL Server database administrators are used to using "dirty" reads in the form of NOLOCK hints or the READ UNCOMMITTED isolation level, in order to achieve high concurrency in busy database systems. Using "dirty reads" exposes queries to the possibility of reading data that is not committed -- and cannot be trusted.

The SNAPSHOT isolation level does away with these issues by providing readers with a previous copy of the row being read if the row is not committed at read time. At the same time, no writers are blocked from updating the row and the reader gets a consistent view of only committed data. In turn writers do not block and are not blocked by other processes. For a thorough discussion of this feature refer to Kimberly Tripp's TechNet white paper on the topic.

Do you have comments on this Ask the Expert Q&A? Let us know.
This was last published in September 2005

Dig Deeper on Microsoft SQL Server 2005

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.