sql-server – 为什么ALTER COLUMN为NOT NULL导致大量日志文件增

我有一个64m行的表,在磁盘上占用4.3 GB的数据.

每行大约30个字节的整数列,以及用于文本的变量NVARCHAR(255)列.

我添加了一个数据类型为Datetimeoffset(0)的NULLABLE列.

然后,我为每一行更新了此列,并确保所有新插入都在此列中放置一个值.

一旦没有NULL条目,我就运行此命令使我的新字段成为必填项：

ALTER TABLE tblCheckResult 
ALTER COLUMN [dtoDateTime] [datetimeoffset](0) NOT NULL

结果是事务日志大小大幅增长 – 从6GB到超过36GB,直到空间不足！

有谁知道SQL Server 2008 R2在这个简单的命令上做了什么导致如此巨大的增长？

解决方法

一个例子.简单表：

DROP TABLE dbo.floob;
GO

CREATE TABLE dbo.floob
(
  id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,bar INT NULL
);

INSERT dbo.floob(bar) SELECT NULL UNION ALL SELECT 4 UNION ALL SELECT NULL;

ALTER TABLE dbo.floob ADD CONSTRAINT df DEFAULT(0) FOR bar

现在,让我们看看页面详细信息.首先,我们需要找出我们正在处理的页面和DB_ID.在我的例子中,我创建了一个名为foo的数据库,DB_ID恰好是5.

DBCC TRACEON(3604,-1);
DBCC IND('foo','dbo.floob',1);
SELECT DB_ID();

输出表明我对第159页感兴趣(DBCC IND输出中的唯一行,PageType = 1).

现在,让我们看看一些选择页面细节,因为我们逐步完成了OP的场景.

DBCC PAGE(5,1,159,3);

UPDATE dbo.floob SET bar = 0 WHERE bar IS NULL;    
DBCC PAGE(5,3);

ALTER TABLE dbo.floob ALTER COLUMN bar INT NOT NULL;
DBCC PAGE(5,3);

现在,我没有得到所有答案,因为我不是一个内心深处的人.但很明显 – 虽然更新操作和NOT NULL约束的添加都无可否认地写入页面 – 后者以完全不同的方式这样做.它似乎实际上改变了记录的结构,而不仅仅是通过替换不可为空的列的可空列来改变位.为什么它必须这样做,我不太确定 – 我想这是一个很好的问题the storage engine team.我相信SQL Server 2012可以更好地处理其中一些场景,FWIW – 但我还没有进行任何详尽的测试.

（编辑：拼字网 - 核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!