表上的DELETE操作

时间:2022-02-26 21:51:24

在今天的文章里,我想给你快速展示下当我们从表里删除记录时,在SQL Server里发生了什么。首先我们来创建一个简单的表,在8KB的页上刚好能插入4条记录。

 -- Create a simple table where 4 records fit onto 1 page
CREATE TABLE TestTable
(
Col1 INT IDENTITY(1, 1),
Col2 CHAR(2000)
)
GO

接下来我们插入4条记录,这样的话一个页刚好全部填满。

 -- Insert 4 records
INSERT INTO TestTable VALUES
(
REPLICATE('', 2000)
),
(
REPLICATE('', 2000)
),
(
REPLICATE('', 2000)
),
(
REPLICATE('', 2000)
)
GO

为了研究我们堆表的细节,我们使用DBCC PAGE命令来倾倒出分配的页面。因此我们还要启用3604跟踪标志,这样的话SQL Server从DBCC PAGE命令直接把结果输入到我们SSMS的会话窗口:

 -- Enable the Trace Flag 3604
DBCC TRACEON(3604)
GO

我们可以使用DBCC IND命令返回所有分配给指定表或索引的页:

 -- Retrieve all pages of the table
DBCC IND(DataModifications, TestTable, -1)
GO

表上的DELETE操作

从输出可以看到,2个页属于我们的表:数据页本身,还有IAM(索引分配图(index allocation map))页。

我这里的页号是118,通过DBCC PAGE命令倾倒出页面:

 -- Dump out one specific page
DBCC PAGE (DataModifications, 1, 118, 2)
GO

当你使用选项2的第3个参数倾倒,SQL Server返回你16进制的页倾倒,包括在页尾所谓的行偏移数组(Row Offset Array),不以任何方式影响数据。

表上的DELETE操作

行偏移数组指向在页上的物理位置,即每条记录存储的地方。第1条记录总是直接存储在页头偏移量96(0x60h)的地方。你也会看到,行偏移数组是逆向增长的。现在让我们从表里删除第2条记录:

 -- Delete a record from the table
DELETE FROM TestTable
WHERE Col1 = 2
GO

通常这里你会期待记录从页里删除。但事实上并不如此:当你再次执行DBCC PAGE命令时,你会看到在页上老记录的内容还是可以看到。在DELETE操作期间,SQL Server唯一做的是,在页尾行偏移数组里,对应的槽无效了。

表上的DELETE操作

如你所见,第2个槽的偏移量是0x0,这是无效的,意味着我们的记录被删除了。在页开始部分,你总会找到96 bytes的页头。现在让我们从表里删除其它的剩余3条记录。

 -- Delete all the remaining records from the table
DELETE FROM TestTable
GO

当你再次用DBCC PAGE命令查看页,你会看到页全部内容还是没改变:每条记录的每个数据在页上还是物理存在的!但是在行偏移数据里每条记录都指向偏移量0x0,这意味着每条记录都被删除。这与你的表是否使用了聚集索引无关——老数据在页上一直存在。
现在的问题是,SQL Server什么时候会初始化页?当你现在插入新的记录,SQL Server会覆盖页的原始内容。但在我们的情况里,这只是物理部分,第1条记录存储的位置。你还是能看到其它“删除”的记录内容。当你在页尾看下行偏移数组,你会看到它已被SQL Server完全初始化了,也意味着你在行偏移数组里你现在只有1个槽了:

表上的DELETE操作

当你下次授权给程序sysadmin特权时,要考虑下这个情况了。使用合适的命令,这些程序还是能看到已经“删除”的数据。

感谢关注!

参考文章:

https://www.sqlpassion.at/archive/2014/02/11/delete-operations-on-tables/