触发器和行版本控制信息

本文介绍了触发器和行版本控制信息的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在什么情况下，表触发器将导致向行末添加14个字节以进行行版本控制？

Under what circumstances will table triggers cause 14 bytes to be added to the end of the row for row versioning?

数据行中使用的空间部分 明确指出每个数据库行在以下位置最多可以使用14个字节行版本信息的行尾...在以下任何一种情况下，第一次修改该行或插入新行时，会添加这14个字节。表具有触发器。

The "Space Used in Data Rows" section on this page clearly states "Each database row may use up to 14 bytes at the end of the row for row versioning information ... These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions ... The table has a trigger."

这在我的测试中没有发生（以下脚本）。在查看数据页面时，我看不到快照隔离下显示的任何版本信息。我是否可以安全地假设数据页上的行将永远不会因为表上的触发器而膨胀了这14个字节？如果不是这样，何时会发生？

This didn't happen in my test (script below). When looking at the data page I don't see any of the versioning info that appears under snapshot isolation. Am I safe in assuming that the rows on data pages will never get bloated by this 14 bytes just because a trigger is on the table? If not when will this occur?

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

推荐答案

所有这些都在在此博客文章中。

我在原始测试中没有看到任何版本控制指针的原因是因为表定义。

The reason why I did not see any versioning pointers in my original test is because of the table definition.

有一个性能优化可以避免添加行版本信息，但前提是表不能生成 ROW_OVERFLOW 或 LOB 分配离子单位。这意味着表的定义必须不允许LOB或可变长度列移出行的可能性。存储的数据的实际大小无关紧要-潜在的大小很重要。

There is a performance optimization that can avoid adding row versioning information, but only if the table cannot generate ROW_OVERFLOW or LOB allocation units. This means that the definition of the table must not allow for LOBs or for the possibility of variable length columns moving off row. The actual size of the data stored is immaterial – it is the potential size that matters.

如果我更改表定义为

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

因此可能不会完全适合行，然后在 DBCC 结果中看到版本指针。例如

So that potentially it might not all fit in row then I do see version pointers in the DBCC results. e.g. as below.

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)