Ok - so we did more partying than we thought so blog posts have been a little sparse this month, but here's one to end off the year.

There's a popular impression that turning on snapshot isolation and then rebuilding indexes will cause all rows in the table to get the extra 14-byte versioning tags. Fact or fiction? Let's find out.

First I'll create a test database, containing a small table with a clustered index and few rows:

CREATE DATABASE SItest;
GO

USE SItest;
GO

CREATE TABLE SmallTable (c1 INT, c2 INT);
CREATE CLUSTERED INDEX SmallTableCI ON SmallTable (c1);
GO

INSERT INTO SmallTable VALUES (1, 1);
INSERT INTO SmallTable VALUES (2,2);
GO

Next I'll turn on READ_COMMITTED_SNAPSHOT and rebuild the index to see if statement level versioning does the trick:

ALTER DATABASE SItest SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER INDEX SmallTableCI ON SmallTable REBUILD;
GO

Now let's look at the data page holding the two rows to see if there's any versioning info (the output is snipped short a little for brevity):

DBCC IND (SItest, SmallTable, 1);
GO

DBCC TRACEON (3604); -- remember this makes the output go to the console
DBCC PAGE (SItest, 1, 153, 3);
GO

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID
------- ----------- ------ ----------- ----------- -----------
1       154         NULL   NULL        2073058421  1
1       153         1      154         2073058421  1

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:153)

<SNIP SNIP SNIP>

Slot 0 Offset 0x60 Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C060

00000000:   10000c00 01000000 01000000 0300f9 ...............
UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

c1 = 1

Slot 0 Column 2 Offset 0x8 Length 4

c2 = 1

Slot 1 Offset 0x6f Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C06F

00000000:   10000c00 02000000 02000000 0300f9 ...............
UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

c1 = 2

Slot 1 Column 2 Offset 0x8 Length 4

c2 = 2

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Nope - both the rows look normal. Now for completeness let's try transaction level versioning and a rebuild:

ALTER DATABASE SItest SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

ALTER INDEX SmallTableCI ON SmallTable REBUILD;
GO

DBCC IND (SItest, SmallTable, 1);
GO

DBCC PAGE (SItest, 1, 143, 3); -- page changed when we rebuilt the index
GO

PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID
------- ----------- ------ ----------- ----------- -----------
1       152         NULL   NULL        2073058421  1
1       143         1      152         2073058421  1

(2 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

PAGE: (1:143)

<SNIP SNIP SNIP>

Slot 0 Offset 0x60 Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C060

00000000:   10000c00 01000000 01000000 0300f9 ...............
UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

c1 = 1

Slot 0 Column 2 Offset 0x8 Length 4

c2 = 1

Slot 1 Offset 0x6f Length 15

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Memory Dump @0x6209C06F

00000000:   10000c00 02000000 02000000 0300f9 ...............
UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

c1 = 2

Slot 1 Column 2 Offset 0x8 Length 4

c2 = 2

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And in another window:

SELECT * FROM smalltable;
GO

c1          c2
----------- -----------
1           1
2           2

(2 row(s) affected)

Cool - so the original values are still there. Let's see the versioning info on the data page (output snipped again):

DBCC PAGE (SItest, 1, 143, 3);
GO

PAGE: (1:143)

<SNIP SNIP SNIP>

Slot 0 Offset 0x7e Length 29

Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFO

Memory Dump @0x61CEC07E

00000000:   5c000c00 01000000 01000000 0300f9e0 \...............
00000010:   00000001 00000057 03000000 00       .......W.....

Version Infomation =
 Transaction Timestamp: 855
 Version Pointer: (file 1 page 224 slot 0)

UNIQUIFIER = [NULL]

Slot 0 Column 1 Offset 0x4 Length 4

c1 = 1

Slot 0 Column 2 Offset 0x8 Length 4

c2 = 1

Slot 1 Offset 0xb8 Length 29

Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFO

Memory Dump @0x61CEC0B8

00000000:   5c000c00 02000000 02000000 0300f9e0 \...............
00000010:   00000001 00010057 03000000 00       .......W.....

Version Infomation =
 Transaction Timestamp: 855
 Version Pointer: (file 1 page 224 slot 1)

UNIQUIFIER = [NULL]

Slot 1 Column 1 Offset 0x4 Length 4

c1 = 2

Slot 1 Column 2 Offset 0x8 Length 4

c2 = 2

Slot 2 Offset 0x9b Length 29

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VERSIONING_INFO

Memory Dump @0x61CEC09B

00000000:   50000c00 04000000 01000000 0300f800 P...............
00000010:   00000000 00000057 03000000 00       .......W.....

Version Information =
 Transaction Timestamp: 855
 Version Pointer: Null

UNIQUIFIER = [NULL]

Slot 2 Column 1 Offset 0x4 Length 4

c1 = 4

Slot 2 Column 2 Offset 0x8 Length 4

c2 = 1

Slot 3 Offset 0xd5 Length 37

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO

Memory Dump @0x61CEC0D5

00000000:   70000c00 04000000 02000000 0300f801 p...............
00000010:   00170001 00000000 00000000 00000057 ...............W
00000020:   03000000 00                         .....

Version Information =
 Transaction Timestamp: 855
 Version Pointer: Null

Slot 3 Column 0 Offset 0x13 Length 4

UNIQUIFIER = 1

Slot 3 Column 1 Offset 0x4 Length 4

c1 = 4

Slot 3 Column 2 Offset 0x8 Length 4

c2 = 2

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I've marked the versioning parts in bold. Notice that the old records have been turned into ghost records too. The second record is now tagged as having variable-length columns too. This is because I updated both records to have the same clustering key value and so the second record now needs a uniquifier - which is stored as a variable-length column.

So, the original statement is a myth - the only time that rows get versioning info added to them is when it's needed to support a versioning operation.