在什么情况下表触发器会导致在行尾添加 14 个字节以进行行版本控制？
本页的“数据行中使用的空间”部分明确指出“每个数据库行可以在行末尾使用最多 14 个字节来存储行版本控制信息...这 14 个字节是在第一次修改行时添加的，或者在插入新行时，在任何这些条件下... 该表有一个触发器。”
这在我的测试中没有发生（下面的脚本）。查看数据页面时，我没有看到快照隔离下出现的任何版本控制信息。我是否可以安全地假设数据页上的行永远不会仅仅因为表上有一个触发器而因这 14 个字节而变得臃肿？如果不是的话，什么时候会发生这种情况？
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
Under what circumstances will table triggers cause 14 bytes to be added to the end of the row for row versioning?
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."
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
发布评论
评论（2）
Paul Randal 有一篇方便的文章，名为：“存储引擎内部：何时添加版本控制标签？”。线索就在标题中:-)
Paul Randal has a handy article called: "Inside the Storage Engine: When do versioning tags get added?". The clue is in the title :-)
这一切都已解释 在这篇博文中。
我在原始测试中没有看到任何版本控制指针的原因是因为表定义。
如果我将表定义更改为
“因此可能无法全部适合行”，那么我确实会在 DBCC 结果中看到版本指针。例如如下。
This is all explained in this blog post.
The reason why I did not see any versioning pointers in my original test is because of the table definition.
If I change the table definition to
So that potentially it might not all fit in row then I do see version pointers in the
DBCCresults. e.g. as below.