Triggers and row version information

Question

Under what circumstances do table triggers force 14 bytes to be added to the end of a row to control versioning of rows?

The section “Space used in data rows” on this page clearly states: “Each database row can use up to 14 bytes at the end of a row for version information of a row ... These 14 bytes are added when the row is first changed or when a new row is added in any of these conditions ... There is a trigger in the table . "

This did not happen in my test (below the script). When viewing a data page, I do not see any version information that appears when snapshots are isolated. Am I really sure that the rows on the data pages will never be inflated by these 14 bytes just because the trigger is on the table? If not, when will this happen?

CREATE DATABASE D2

GO

ALTER DATABASE D2 SET ALLOW_SNAPSHOT_ISOLATION OFF

USE D2;

GO

CREATE TABLE T1
(
F1 INT IDENTITY(1,1) PRIMARY KEY,
F2 INT,
V1 VARCHAR(100)
)

INSERT INTO T1
SELECT TOP 80 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS F2,
              REPLICATE(CHAR((ROW_NUMBER() OVER (ORDER BY (SELECT 0) -1) % 26) + ASCII('A')),100) AS V1
FROM sys.all_columns           

GO      

CREATE TRIGGER TR
   ON  T1
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM inserted

END
GO

UPDATE T1 SET F2=F2+1

GO

DECLARE @DBCCPAGE nvarchar(100)

SELECT TOP 1  @DBCCPAGE = 'DBCC PAGE(''D2'',' + CAST(file_id AS VARCHAR) + ',' + CAST(page_id AS VARCHAR) + ',3)'
FROM T1
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) 

DBCC TRACEON(3604)
EXEC (@DBCCPAGE)


GO

Answer 1

All of this is explained in this blog post .

The reason I didn't see version pointers in my original test is due to the definition of the table.

, , ROW_OVERFLOW LOB. , LOB , . - , .

CREATE TABLE T1
(
F1 INT IDENTITY(1,1) PRIMARY KEY,
F2 INT,
V1 VARCHAR(1000),
V2 VARCHAR(8000) NULL
)

, DBCC. .

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 133                    
Memory Dump @0x63A4CC92

00000000:   70000c00 03000000 04000000 04004801 †p.............H.         
00000010:   00770044 44444444 44444444 44444444 †.w.DDDDDDDDDDDDD         
00000020:   44444444 44444444 44444444 44444444 †DDDDDDDDDDDDDDDD         
00000030:   44444444 44444444 44444444 44444444 †DDDDDDDDDDDDDDDD         
00000040:   44444444 44444444 44444444 44444444 †DDDDDDDDDDDDDDDD         
00000050:   44444444 44444444 44444444 44444444 †DDDDDDDDDDDDDDDD         
00000060:   44444444 44444444 44444444 44444444 †DDDDDDDDDDDDDDDD         
00000070:   44444444 444444c8 7b000001 000500b8 †DDDDDDDÈ{......¸         
00000080:   00000000 00††††††††††††††††††††††††††.....                    

Version Information = 
    Transaction Timestamp: 184
    Version Pointer: (file 1 page 31688 currentSlotId 5)

Answer 2

: " : ?" . : -)