SQLSERVER 的 truncate 和 delete 区别解析（sqlserver 时间加减）原创

随心笔谈2年前发布编辑

176 0

🌐 经济型：买域名、轻量云服务器、用途：游戏网站等《腾讯云》特点：特价机便宜适合初学者用点我优惠购买

🚀 拓展型：买域名、轻量云服务器、用途：游戏网站等《阿里云》特点：中档服务器便宜域名备案事多点我优惠购买

🛡️ 稳定型：买域名、轻量云服务器、用途：游戏网站等《西部数码》特点：比上两家略贵但是稳定性超好事也少点我优惠购买

目录一：背景1. 讲故事二：区别详解1. 思考2. 观察 delete 的事务日志。3. 观察 truncate 的事务日志。三：GAM 空间管理1. 基本原理四：总结

在面试中我相信有很多朋友会被问到，这是一个很有意思的话题，本篇我就试着来回答一下，如果下次大家遇到这类问题，我的答案应该可以帮你成功度过吧。

从宏观角度来说, 是 DML 语句, 是 DDL 语句，这些对数据库产生破坏类的语句肯定是要被跟踪的，言外之意就是在某些场景下可以被回滚的，既然可以被，那自然就会产生，所以从的角度入手会是一个好的办法。

为了方便测试，还是用上一篇的表，创建好之后插入10条记录，参考sql如下：

DROP TABLE dbo.post;
CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT ‘aaaaaa’)

INSERT post DEFAULT VALUES
GO 10

有了数据之后就可以通过函数从中提取事务日志来观察 delete 和 truncate 日志的不同点。

为了观察产生的日志，这里用记录一下起始点，参考sql如下：

DECLARE @max_lsn VARCHAR(100)
SELECT @max_lsn=[Current LSN] FROM fn_dblog(NULL,NULL)
DELETE FROM post;
SELECT * FROM fn_dblog(NULL,NULL) WHERE [Current LSN] >@max_lsn

从事务日志看，主要做了两件事情。

10 行 delete 记录删除

这里就有一个好奇的地方了，sqlserver 是如何执行删除操作的呢？要回答这个问题需要到数据页上找答案，参考sql如下：

DBCC IND(MyTestDB,post,-1)
DBCC PAGE(MyTestDB,1,240,2)

从图中可以得到如下两点信息, 至少在堆表下 delete 操作并没有删除 Page，第二个是 delete 记录删除只是将 slot 的指针。

有些朋友可能要问，为什么还有对的操作呢？很简单它就是用来记录当前页面的的，可以看下我的上一篇文章。

delete 原理搞清楚之后，接下来看下做了什么？参考sql 如下：

DROP TABLE dbo.post;
CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT ‘aaaaaa’)

INSERT post DEFAULT VALUES
GO 10

DECLARE @max_lsn VARCHAR(100)
SELECT @max_lsn=[Current LSN] FROM fn_dblog(NULL,NULL)
TRUNCATE TABLE dbo.post
SELECT [Current LSN],Operation,Context,AllocUnitName FROM fn_dblog(NULL,NULL) WHERE [Current LSN] >@max_lsn

从图中可以看到，truncate 主要是对 , , 三个空间管理数据页做了修改，并没有涉及到页，那就有一个疑问了,我的PAGE页还在吗？可以用看下。

我去，操作居然把我的页给弄丢了，它是怎么实现的呢？要想找到答案，大家可以想一想， truncate 是一个 DDL 语句，为了快速释放表数据，它干脆把和的关系给切断了，如果大家有点懵，画个图大概就是下面这样。

为了验证这个结论，可以用直接导出号数据页，观察下是不是表中的数据，不过遗憾的是，这个数据页已不归属 post 表了。。。

接下来又得回答另外一个问题，sqlserver 是如何切断的？这里就需要理解空间管理机制。

GAM 是用来跟踪状态的数据页，它是用一个 bit 位跟踪一个 , 在数据库中一个区表示，在 GAM 数据页中，用 1 表示可分配的初始状态，用 0 表示已分配状态，可能大家有点懵，我再画个简图吧。

为了让大家眼见为实，还是用给大家做个演示。

DROP TABLE dbo.post;
CREATE TABLE post (id INT IDENTITY, content CHAR(1000) DEFAULT ‘aaaaaa’)
INSERT post DEFAULT VALUES
GO 10

DBCC TRACEON(3604)
DBCC IND(MyTestDB,post,-1)

从图中可以看到，post 表分配的数据页是和号，对应的区号就是，因为 GAM 是用 1bit 来跟踪一个区，所以理论上 GAM 页面偏移 31bit 的位置就标记了该区的分配情况。

这么说可能大家又有点懵，我准备用 windbg 来演示一下，首先大家要记住 GAM 是 mdf 文件中的第三个页面，用表示，前两个分别是文件头和 PFS 页，关于页面的首地址可以用导出来。

0:078> dp 00000009009F8000 +0x60 00000009`009f8060 00000000`005e0000 00000000`00000000 00000009`009f8070 00000000`00000000 00000000`00000000 00000009`009f8080 00000000`00000000 00000000`00000000 00000009`009f8090 00000000`00000000 00000000`00000000 00000009`009f80a0 00000000`00000000 00000000`00000000 00000009`009f80b0 00000000`00000000 00000000`00000000 00000009`009f80c0 d0180000`00001f38 ffffffff`ffffffd1 00000009`009f80d0 ffffffff`ffffffff ffffffff`ffffffff

从输出内容看，那个就是 bitmap 数组的长度，后面就是 bit 的占用情况，因为在 31 bit 上，我们观察一个 int 就好了，输出如下：

从图中可以看到，全部都是 0 也就说明当前都是分配状态，如果是 1 表示未分配，接下来把 post 给 truncate 掉再次观察 GAM 页。

TRUNCATE TABLE dbo.post;
DBCC PAGE(MyTestDB,1,2,2)

输出如下：

0:117> dp 00000009009F8000+0x60 00000009`009f8060 00000000`005e0000 00000000`00000000 00000009`009f8070 00000000`00000000 00000000`00000000 00000009`009f8080 00000000`00000000 00000000`00000000 00000009`009f8090 00000000`00000000 00000000`00000000 00000009`009f80a0 00000000`00000000 00000000`00000000 00000009`009f80b0 00000000`00000000 00000000`00000000 00000009`009f80c0 d0184000`00001f38 ffffffff`ffffffd1 00000009`009f80d0 ffffffff`ffffffff ffffffff`ffffffff

对比之后会发现由原来的变成了，可以用 .format 来格式化下。

从图中看 31bit 跟踪的第 31 号区被回收了，也就验证了真的切断了联系。

同样的道理 PFS 偏移的位置跟踪的这个页面也是被释放状态。

总的来说，delete 操作是将数据页中的每个 slot 指针一条一条的擦掉，每次擦除都会产生一条事务日志，所以对海量数据进行会产生海量的事务日志,导致你的日志文件暴增。而 truncate 是直接切断 post 和 page 的联系，只需要修改几个空间管理页的 bit 位即可。

最后的建议是如果要清空表数据，建议用。

到此这篇关于SQLSERVER 的 truncate 和 delete 有区别吗？的文章就介绍到这了,更多相关sqlserver truncate 和 delete 区别内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

您可能感兴趣的文章:sqlserver中drop、truncate和delete语句的用法sqlserver 日志恢复方法(搞定drop和truncate)SqlServer2008误操作数据(delete或者update)后恢复数据的方法sqlserver中delete、update中使用表别名和oracle的区别SQLServer 2008中SQL增强之三 Merge(在一条语句中使用Insert,Update,Delete)