A few weeks ago Kalen Delaney presented a 3-day seminar in Israel. Unfortunately, I wasn’t able to attend the seminar, but some of our consultants attended and returned from the seminar with some interesting insights.
The first day was about concurrency control. Among other things, Kalen spoke about the snapshot isolation level and how row versioning works when this isolation level is used.
One question was remained unanswered. Suppose you have an index on column “Col1”, and you update this column while a snapshot transaction is open. The index key is, of course, updated and moved to the new position in the index, and it also now has an additional 14-bytes pointer to the versioning info in tempdb. As long as queries in the snapshot transaction access the table, they will retrieve the old value from the version store. Let’s assume you updated the column from 2 to 5. Now, what happens if a query in the snapshot transaction tries to retrieves the rows with Col1 = 2? The information about the previous value (2) is stored in tempdb, and the pointer is stored in the index under the new value (5). So if a query performs an index seek in order to retrieve only the rows with a value of 2, how would SQL Server know about the old value stored in tempdb?
Let’s look at an example. First, here is the setup:
USE master; GO -- Create the SnapshotTest database IF DB_ID (N'SnapshotTest') IS NOT NULL BEGIN ALTER DATABASE SnapshotTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE SnapshotTest; END; GO CREATE DATABASE SnapshotTest; GO ALTER DATABASE SnapshotTest SET ALLOW_SNAPSHOT_ISOLATION ON; GO -- Create the "dbo.Test" table USE SnapshotTest; GO CREATE TABLE dbo.Test ( Col1 INT NOT NULL , Col2 CHAR(5) NOT NULL ); GO -- Populate the "dbo.Test" table with 10 consecutive rows INSERT INTO dbo.Test WITH (TABLOCK) ( Col1 , Col2 ) SELECT TOP (10) Col1 = ROW_NUMBER () OVER (ORDER BY (SELECT NULL) ASC) , Col2 = N'XXXXX' FROM sys.all_columns; GO SELECT Col1 , Col2 FROM dbo.Test ORDER BY Col1 ASC; GO -- Create a non-clustered index on the "Col1" column CREATE NONCLUSTERED INDEX ix_Test_nc_nu_Col1 ON dbo.Test (Col1 ASC); GO