主页 / dba / 问题 / 130911
Accepted
Akash
Asked: 2016-03-02 06:50:37 +0800 CST

为什么要立即添加具有默认约束的 NOT NULL 列？

  • 772
CREATE TABLE TestTab (ID INT IDENTITY(1,1), st nvarchar(100))

INSERT INTO TestTab (st) values ('a')
INSERT INTO TestTab (st) values ('b')
INSERT INTO TestTab (st) values ('c')
INSERT INTO TestTab (st) values ('d')
INSERT INTO TestTab (st) values ('e')

INSERT INTO TestTab (st) SELECT TOP 10000 st from testtab
GO 30

ALTER TABLE TestTab ADD newcol nvarchar(10) DEFAULT 'newcol'
UPDATE TestTab SET newcol = 'newcol'  --6 sec
ALTER TABLE TestTab ADD newcol1 nvarchar(10) DEFAULT 'newcol1' NOT NULL

DROP TABLE TestTab

当我执行这个测试脚本时，ALTERwithUPDATE需要 6 秒，这是可以理解的。

但是，即使在更大的表上， ALTERwith也会立即执行。DEFAULT NOT NULL有什么解释为什么这是瞬时的吗？在物理磁盘上，数据仍然需要写入所有行对吗？

我尝试查看SET STATISTICS IO ON查询计划，但是这些似乎不适用于 DDL 操作。

sql-server sql-server-2012

1 个回答

  1. Best Answer

    是的，添加一个具有 NOT NULL 和默认值的列实际上并不会在更改时将值写入所有行，因此它不再是数据大小操作。当您从表中选择时，这些列实际上是从sys.system_internals_partition_columns 实现的，这可以防止必须写入所有值（直到它们被更改）。请注意，这不适用于所有数据类型，需要企业版。

    Remus Rusanu在这里更详细地解释了这一点：

    此外，ALTER至少，我们仍然无法向您显示计划，因为 SQL Server 不生成计划，但要查看 I/O，您可以使用SQL Sentry Plan Explorer。* 此屏幕截图显示添加列 c5 , "online" 如上所述，然后是另一列 c6, "offline"，因为不支持 LOB 类型。您可以看到 I/O 主要表示为读取而不是写入，但更能说明问题的是UPDATE与离线更改相关联的（无效！）。

    在线与离线更改的 I/O

    如果您没有企业版，则两个语句都将UPDATE附加辅助（以及相关的读取）。（如果您使用免费版本的 Plan Explorer，它没有获得完整的查询调用堆栈，您将看不到上面的内容 - 您只会看到一个空的语句树。需要付费版本才能看到完整的查询调用堆栈。）

    请注意，SQL Server 会生成一个估计的计划，但它不是很有用。完全没有。在线变更的预计计划与离线变更的预计计划相同。

    在线修改方案图

    *免责声明：我为 SQL Sentry 工作。

    • 23

相关问题