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.
