為什麼要立即添加具有預設約束的 NOT NULL 列？

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 操作。

是的，添加一個具有 NOT NULL 和預設值的列實際上並不會在更改時將值寫入所有行，因此它不再是數據大小操作。當您從表中選擇時，這些列實際上是從sys.system_internals_partition_columns 實現的，這可以防止必須寫入所有值（直到它們被更改）。請注意，這不適用於所有數據類型，需要企業版。

Remus Rusanu在這裡更詳細地解釋了這一點：

• 在 SQL Server 2012 中添加帶有值列的線上非 NULL

此外，ALTER至少，我們仍然無法向您顯示計劃，因為 SQL Server 不生成計劃，但要查看 I/O，您可以使用SQL Sentry Plan Explorer。* 此螢幕截圖顯示添加列 c5 , “online” 如上所述，然後是另一列 c6, “offline”，因為不支持 LOB 類型。您可以看到 I/O 主要表示為讀取而不是寫入，但更能說明問題的是UPDATE與離線更改相關聯的（無效！）。

如果您沒有企業版，則兩個語句都將UPDATE附加輔助（以及相關的讀取）。（如果您使用免費版本的 Plan Explorer，它沒有獲得完整的查詢呼叫堆棧，您將看不到上面的內容 - 您只會看到一個空的語句樹。需要付費版本才能看到完整的查詢呼叫堆棧。）

請注意，SQL Server 會生成一個估計的計劃，但它不是很有用。完全沒有。線上變更的預計計劃與離線變更的預計計劃相同。

*免責聲明：我為 SQL Sentry 工作。

引用自：https://dba.stackexchange.com/questions/130911