触发器和行版本控制信息 [英] Triggers and row versioning information

问题描述

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

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

数据行中使用的空间部分<此页面上的href = http://msdn.microsoft.com/zh-cn/library/ms175492.aspx rel = nofollow> 明确指出每个数据库行在以下位置最多可以使用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)

这篇关于触发器和行版本控制信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！