ALTER TABLE … DROP COLUMN 真的是僅元數據操作嗎？
我發現幾個來源表明 ALTER TABLE … DROP COLUMN 是僅元數據操作。
怎麼會這樣？DROP COLUMN 期間的數據是否不需要從底層非聚集索引和聚集索引/堆中清除？
此外，為什麼Microsoft Docs暗示它是完全記錄的操作？
對錶所做的修改會被記錄並完全恢復。影響大型表中所有行的更改（例如刪除列或在某些版本的 SQL Server 上添加具有預設值的 NOT NULL 列）可能需要很長時間才能完成並生成許多日誌記錄。像影響許多行的任何 INSERT、UPDATE 或 DELETE 語句一樣小心執行這些 ALTER TABLE 語句。
作為第二個問題：如果數據沒有從底層頁面中刪除，引擎如何跟踪刪除的列？
在某些情況下，刪除列可能是元數據操作。任何給定表的列定義不包含在儲存行的每個頁面中，列定義僅儲存在數據庫元數據中，包括 sys.sysrowsets、sys.sysrscols 等。
當刪除未被任何其他對象引用的列時，儲存引擎通過從各種系統表中刪除相關詳細資訊來簡單地將列定義標記為不再存在。刪除元數據的操作使過程記憶體無效，每當查詢隨後引用該表時都需要重新編譯。由於重新編譯只返回表中目前存在的列，因此甚至從不要求刪除列的列詳細資訊；儲存引擎跳過該列在每一頁中儲存的字節，就好像該列不再存在一樣。
當對錶進行後續 DML 操作時，受影響的頁面將被重寫，而刪除的列的數據不包含在內。如果重建聚集索引或堆，則刪除列的所有字節自然不會寫回磁碟上的頁面。隨著時間的推移，這有效地分散了刪除列的負載，使其不那麼明顯。
在某些情況下，您無法刪除列，例如當列包含在索引中時，或者當您手動為列創建統計對象時。我寫了一篇部落格文章，顯示了嘗試使用手動創建的統計對象更改列時出現的錯誤。刪除列時適用相同的語義 - 如果該列被任何其他對象引用，則不能簡單地刪除它。必須先更改引用對象，然後才能刪除該列。
這很容易通過在刪除一列後查看事務日誌的內容來顯示。下面的程式碼創建了一個包含單個 8,000 長字元列的表。它添加一行，然後刪除它，並顯示適用於刪除操作的事務日誌的內容。日誌記錄顯示對儲存表和列定義的各種系統表的修改。如果列數據實際上是從分配給表的頁面中刪除的，您會看到記錄實際頁面數據的日誌記錄；沒有這樣的記錄。
DROP TABLE IF EXISTS dbo.DropColumnTest; GO CREATE TABLE dbo.DropColumnTest ( rid int NOT NULL CONSTRAINT DropColumnTest_pkc PRIMARY KEY CLUSTERED , someCol varchar(8000) NOT NULL ); INSERT INTO dbo.DropColumnTest (rid, someCol) SELECT 1, REPLICATE('Z', 8000); GO DECLARE @startLSN nvarchar(25); SELECT TOP(1) @startLSN = dl.[Current LSN] FROM sys.fn_dblog(NULL, NULL) dl ORDER BY dl.[Current LSN] DESC; DECLARE @a int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), LEFT(@startLSN, 8), 0), 1) , @b int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1) , @c int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), RIGHT(@startLSN, 4), 0), 1); SELECT @startLSN = CONVERT(varchar(8), @a, 1) + ':' + CONVERT(varchar(8), @b, 1) + ':' + CONVERT(varchar(8), @c, 1) ALTER TABLE dbo.DropColumnTest DROP COLUMN someCol; SELECT * FROM sys.fn_dblog(@startLSN, NULL) --modify an existing data row SELECT TOP(1) @startLSN = dl.[Current LSN] FROM sys.fn_dblog(NULL, NULL) dl ORDER BY dl.[Current LSN] DESC; SET @a = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), LEFT(@startLSN, 8), 0), 1); SET @b = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1); SET @c = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), RIGHT(@startLSN, 4), 0), 1); SELECT @startLSN = CONVERT(varchar(8), @a, 1) + ':' + CONVERT(varchar(8), @b, 1) + ':' + CONVERT(varchar(8), @c, 1) UPDATE dbo.DropColumnTest SET rid = 2; SELECT * FROM sys.fn_dblog(@startLSN, NULL)（輸出太大，無法在此處顯示，dbfiddle.uk 不允許我訪問 fn_dblog）
第一組輸出顯示日誌是 DDL 語句刪除列的結果。第二組輸出顯示執行我們更新
rid列的 DML 語句後的日誌。在第二個結果集中，我們看到指示對 dbo.DropColumnTest 進行刪除的日誌記錄，然後是對 dbo.DropColumnTest 的插入。每個日誌記錄長度為 8116，表示實際頁面已更新。從上面測試中的命令輸出可以看出
fn_dblog，整個操作都被完全記錄了下來。這適用於簡單恢復以及完全恢復。術語“完全記錄”可能會被誤解為未記錄數據修改。這不是發生的情況 - 修改被記錄，並且可以完全回滾。日誌只是簡單地記錄被觸摸的頁面，並且由於 DDL 操作沒有記錄表的數據頁DROP COLUMN，因此無論表的大小如何，可能發生的任何回滾都將非常迅速地發生。為了科學，下面的程式碼將轉儲上面程式碼中包含的表格的數據頁，使用
DBCC PAGE樣式“3”。樣式“3”表示我們想要頁眉加上詳細的每行解釋。該程式碼使用游標顯示表中每個頁面的詳細資訊，因此您可能需要確保不要在大表上執行它。DBCC TRACEON(3604); --directs out from DBCC commands to the console, instead of the error log DECLARE @dbid int = DB_ID(); DECLARE @fileid int; DECLARE @pageid int; DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT dpa.allocated_page_file_id , dpa.allocated_page_page_id FROM sys.schemas s INNER JOIN sys.objects o ON o.schema_id = s.schema_id CROSS APPLY sys.dm_db_database_page_allocations(DB_ID(), o.object_id, NULL, NULL, 'DETAILED') dpa WHERE o.name = N'DropColumnTest' AND s.name = N'dbo' AND dpa.page_type_desc = N'DATA_PAGE'; OPEN cur; FETCH NEXT FROM cur INTO @fileid, @pageid; WHILE @@FETCH_STATUS = 0 BEGIN DBCC PAGE (@dbid, @fileid, @pageid, 3); FETCH NEXT FROM cur INTO @fileid, @pageid; END CLOSE cur; DEALLOCATE cur; DBCC TRACEOFF(3604);查看展示中第一頁的輸出（在刪除列之後，但在更新列之前），我看到了：
頁：（1：100104） 緩衝： BUF @0x0000021793E42040 bpage = 0x000002175A7A0000 bhash = 0x0000000000000000 bpageno = (1:100104) bdbid = 10 breferences = 1 bcputicks = 0 bsampleCount = 0 bUse1 = 13760 bstat = 0x10b 部落格 = 0x212121cc bnext = 0x0000000000000000 bDirtyContext = 0x000002175004B640 bstat2 = 0x0 頁眉： 頁面@0x000002175A7A0000 m_pageId = (1:100104) m_headerVersion = 1 m_type = 1 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0xc000 m_objId (AllocUnitId.idObj) = 300 m_indexId (AllocUnitId.idInd) = 256 元數據：AllocUnitId = 72057594057588736 元數據：PartitionId = 72057594051756032 元數據：IndexId = 1 元數據：ObjectId = 174623665 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 8 m_slotCnt = 1 m_freeCnt = 79 m_freeData = 8111 m_reservedCnt = 0 m_lsn = (616:14191:25) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 0 數據庫碎片 ID = 1 分配狀態 GAM (1: 2) = 已分配 SGAM (1: 3) = 未分配 PFS (1:97056) = 0x40 已分配 0_PCT_FULL DIFF (1:6) = 已更改 ML (1:7) = 不是 MIN_LOGGED 插槽 0 偏移量 0x60 長度 8015 記錄類型 = PRIMARY_RECORD 記錄屬性 = NULL_BITMAP VARIABLE_COLUMNS 記錄大小 = 8015 記憶體轉儲@0x000000B75227A060 0000000000000000: 30000800 01000000 02000001 004f1f5a 5a5a5a5a 0............O.ZZZZZ 0000000000000014: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZ . . . 0000000000001F2C: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZ 0000000000001F40: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a ZZZZZZZZZZZZZZZ 插槽 0 列 1 偏移量 0x4 長度 4 長度（物理） 4 擺脫 = 1 插槽 0 列 67108865 偏移量 0xf 長度 0 長度（物理） 8000 已刪除 = NULL 插槽 0 偏移 0x0 長度 0 長度（物理） 0 KeyHashValue = (8194443284a0)為簡潔起見，我已經從上面顯示的輸出中刪除了大部分原始頁面轉儲。在輸出的末尾，您將在
rid列中看到：插槽 0 列 1 偏移量 0x4 長度 4 長度（物理） 4 擺脫 = 1上面的最後一行
rid = 1，返回列的名稱，以及儲存在頁面上的列中的目前值。接下來，您將看到：
插槽 0 列 67108865 偏移量 0xf 長度 0 長度（物理） 8000 已刪除 = NULL輸出顯示 Slot 0 包含一個已刪除的列，這取決於
DELETED列名通常所在的文本。NULL由於該列已被刪除，因此返回該列的值。但是，正如您在原始數據中看到的那樣，REPLICATE('Z', 8000)該列的 8,000 個字元長的值 , 仍然存在於頁面上。這是 DBCC PAGE 輸出的那部分範例：0000000000001EDC: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZ 0000000000001EF0: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZ 0000000000001F04: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZ 0000000000001F18: 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a 5a5a5a5a ZZZZZZZZZZZZZZZZZZZ