將列從 NOT NULL 更改為 NULL - 幕後發生了什麼？

January 3, 2017

我們有一個包含 2.3B 行的表。我們想將一列從 NOT NULL 更改為 NULL。該列包含在一個索引中（不是聚集索引或 PK 索引）。數據類型沒有改變（它是一個 INT）。只是可空性。聲明如下：
Alter Table dbo.Workflow Alter Column LineId Int NULL
該操作在我們停止之前需要超過 10 次（我們甚至還沒有讓它執行完成，因為它是一個阻塞操作並且花費了太長時間）。我們可能會將表複製到開發伺服器以測試實際需要多長時間。但是，我很好奇是否有人知道 SQL Server 在從 NOT NULL 轉換為 NULL 時在後台做什麼？此外，受影響的索引是否需要重建？生成的查詢計劃並不表明發生了什麼。
有問題的表是聚集的（不是堆）。

正如@Souplex 在評論中提到的那樣，一個可能的解釋可能是如果該列是NULL它參與的非聚集索引中的第一個-able 列。
對於以下設置
CREATE TABLE Foo
 (
    A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
    B CHAR(1) NOT NULL DEFAULT 'B'
 )

CREATE NONCLUSTERED INDEX ix
 ON Foo(B);

INSERT INTO Foo
           (B)
SELECT TOP 100000 'B'
FROM   master..spt_values v1,
      master..spt_values v2 
sys.dm_db_index_physical_stats 顯示非聚集索引ix有 248 個葉頁和一個根頁。
索引葉頁中的典型行如下所示
並在根頁面
然後跑…
CHECKPOINT;

GO

ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;


SELECT Operation, 
      Context,
      ROUND(SUM([Log Record Length]) / 1024.0,1) AS [Log KB],
      COUNT(*) as [OperationCount]
FROM sys.fn_dblog(NULL,NULL)
WHERE AllocUnitName = 'dbo.Foo.ix'
GROUP BY Operation, Context
回來
+-----------------+--------------------+-------------+----------------+
|    Operation    |      Context       |   Log KB    | OperationCount |
+-----------------+--------------------+-------------+----------------+
| LOP_SET_BITS    | LCX_GAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_IAM            | 0.100000    |              1 |
| LOP_SET_BITS    | LCX_IAM            | 4.200000    |             69 |
| LOP_FORMAT_PAGE | LCX_INDEX_INTERIOR | 8.700000    |              3 |
| LOP_FORMAT_PAGE | LCX_INDEX_LEAF     | 2296.200000 |            285 |
| LOP_MODIFY_ROW  | LCX_PFS            | 16.300000   |            189 |
+-----------------+--------------------+-------------+----------------+
再次檢查索引葉，行現在看起來像
以及上層頁面中的行如下。
每行都已更新，現在包含兩個字節用於列計數以及另一個字節用於 NULL_BITMAP。
由於額外的行寬，非聚集索引現在有 285 個葉頁，現在有兩個中間級頁以及根頁。
的執行計劃
ALTER TABLE Foo ALTER COLUMN B  CHAR(1) NULL;
看起來如下
這會創建一個全新的索引副本，而不是更新現有的並需要拆分頁面。