"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.
Dig Deeper on Microsoft SQL Server 2005
Related Q&A from Adam Machanic
Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic ... Continue Reading
Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the ... Continue Reading
SQL Server 2005 and T-SQL do have differences. The advantages and disadvantages would depend user environment as described by SQL Server 2005 expert... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.