ALTER TABLE ... DROP COLUMN 真的是仅元数据操作吗？

在某些情况下，删除列可能是元数据操作。任何给定表的列定义不包含在存储行的每个页面中，列定义仅存储在数据库元数据中，包括 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