SQL SERVER-Delete和Truncate的区别

背景

一般在删除表数据时候，通常会有执行两个SQL语句：delete和truncate，有条件的删除我们平时都会用delete，而如果全部删除，那我们通常都会选择truncate，因为这个大家都知道，delete会一条条删除，全删除条件下，truncate比delete速度快很多，但是到底是怎么样的，这个就不太清楚，那我们今天就来研究下两者具体的区别

操作

样例1：测试delete

首先创建3个各有1000行数据的表，Fact_Sale_1，Fact_Sale_2，Fact_Sale_3，其中，Fact_Sale_2创建一个聚集索引，Fact_Sale_3创建一个非聚集索引，如下：

--直接将数据插入表，不是用任何索引SELECT TOP 1000    *INTO [Test].[dbo].Fact_Sale_1FROM [DW_HQ].[dbo].[Fact_Sale] AS a WITH(NOLOCK)GOSELECT TOP 1000    *INTO [dbo].Fact_Sale_2FROM [DW_HQ].[dbo].[Fact_Sale] AS a WITH(NOLOCK)GO--创建聚集索引CREATE CLUSTERED INDEX CLUS_IX_Fact_Sale_2_Datekey ON [dbo].[Fact_Sale_2]([Datekey])GOSELECT TOP 1000    *INTO [dbo].Fact_Sale_3FROM [DW_HQ].[dbo].[Fact_Sale] AS a WITH(NOLOCK)GO--创建非聚集索引CREATE NONCLUSTERED INDEX NONCLUS_IX_Fact_Sale_3_Datekey ON [dbo].[Fact_Sale_3]([Datekey])GO

然后我们查看一下各个表的索引情况

EXEC [sys].[sp_helpindex] @objname = N'Fact_Sale_1' -- nvarchar(776)GO/*结果  对象 'Fact_Sale_1' 没有任何索引，或者您没有所需的权限。*/EXEC [sys].[sp_helpindex] @objname = N'Fact_Sale_2' -- nvarchar(776)GO/*结果index_name                     index_description               index_keysCLUS_IX_Fact_Sale_2_Datekey    clustered located on PRIMARY    Datekey*/EXEC [sys].[sp_helpindex] @objname = N'Fact_Sale_3' -- nvarchar(776)GO/*index_name                        index_description                  index_keysNONCLUS_IX_Fact_Sale_3_Datekey    nonclustered located on PRIMARY    Datekey*/接着我们使用delete命令删除这个三个表的数据DELETE FROM [dbo].[Fact_Sale_1]GODELETE FROM [dbo].[Fact_Sale_2]GODELETE FROM [dbo].[Fact_Sale_3]GO

使用DBCC SHOWCONTIG()查看我们删除后的数据表存储情况

/*DBCC SHOWCONTIG 正在扫描 'Fact_Sale_1' 表...表: 'Fact_Sale_1' (2105058535)；索引 ID: 0，数据库 ID: 34已执行 TABLE 级别的扫描。- 扫描页数................................: 30- 扫描区数..............................: 5- 区切换次数..............................: 4- 每个区的平均页数........................: 6.0- 扫描密度 [最佳计数:实际计数].......: 80.00% [4:5]- 区扫描碎片 ..................: 20.00%- 每页的平均可用字节数.....................: 8029.3- 平均页密度(满).....................: 0.80%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。*//*DBCC SHOWCONTIG 正在扫描 'Fact_Sale_2' 表...表: 'Fact_Sale_2' (2121058592)；索引 ID: 1，数据库 ID: 34已执行 TABLE 级别的扫描。- 扫描页数................................: 1- 扫描区数..............................: 1- 区切换次数..............................: 0- 每个区的平均页数........................: 1.0- 扫描密度 [最佳计数:实际计数].......: 100.00% [1:1]- 逻辑扫描碎片 ..................: 0.00%- 区扫描碎片 ..................: 0.00%- 每页的平均可用字节数.....................: 7865.0- 平均页密度(满).....................: 2.83%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。*//*DBCC SHOWCONTIG 正在扫描 'Fact_Sale_3' 表...表: 'Fact_Sale_3' (2137058649)；索引 ID: 0，数据库 ID: 34已执行 TABLE 级别的扫描。- 扫描页数................................: 30- 扫描区数..............................: 5- 区切换次数..............................: 4- 每个区的平均页数........................: 6.0- 扫描密度 [最佳计数:实际计数].......: 80.00% [4:5]- 区扫描碎片 ..................: 20.00%- 每页的平均可用字节数.....................: 8029.3- 平均页密度(满).....................: 0.80%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。*/

可以看到，没有聚集索引的表delete之后分区和页数都是一致的都是30和5，而有聚集索引的表只有1个分区和1个页数，并且这些空间都是空的，没有聚集索引，删除后就会造成我们平时删除数据后空间上造成极大的浪费。

样例2：测试truncate

同样创建上述3个表，一样的创建索引和非聚集索引，然后我们使用truncate命令删除所有的数据，在使用DBCC SHOWCONTIG()来查看数据表存储情况，如下：

/*DBCC SHOWCONTIG 正在扫描 'Fact_Sale_1' 表...表: 'Fact_Sale_1' (21575115)；索引 ID: 0，数据库 ID: 34已执行 TABLE 级别的扫描。- 扫描页数................................: 0- 扫描区数..............................: 0- 区切换次数..............................: 0- 每个区的平均页数........................: 0.0- 扫描密度 [最佳计数:实际计数].......: 100.00% [0:0]- 区扫描碎片 ..................: 0.00%- 每页的平均可用字节数.....................: 0.0- 平均页密度(满).....................: 0.00%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。*//*DBCC SHOWCONTIG 正在扫描 'Fact_Sale_2' 表...表: 'Fact_Sale_2' (37575172)；索引 ID: 1，数据库 ID: 34已执行 TABLE 级别的扫描。- 扫描页数................................: 0- 扫描区数..............................: 0- 区切换次数..............................: 0- 每个区的平均页数........................: 0.0- 扫描密度 [最佳计数:实际计数].......: 100.00% [0:0]- 逻辑扫描碎片 ..................: 0.00%- 区扫描碎片 ..................: 0.00%- 每页的平均可用字节数.....................: 0.0- 平均页密度(满).....................: 0.00%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。*//*DBCC SHOWCONTIG 正在扫描 'Fact_Sale_3' 表...表: 'Fact_Sale_3' (53575229)；索引 ID: 0，数据库 ID: 34已执行 TABLE 级别的扫描。- 扫描页数................................: 0- 扫描区数..............................: 0- 区切换次数..............................: 0- 每个区的平均页数........................: 0.0- 扫描密度 [最佳计数:实际计数].......: 100.00% [0:0]- 区扫描碎片 ..................: 0.00%- 每页的平均可用字节数.....................: 0.0- 平均页密度(满).....................: 0.00%DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。*/

可以看到，3个表的分区、页数都为0，空间也为0,。

因此：

1、truncate比delete所用的事务日志空间更少：
delete是一行一行操作，并且把记录都存进日志文件（无论任何恢复模式，都会记录日志）。而truncate操作，是对一个页操作，在日志中，仅仅记录释放页面的这个动作，而不记录每一行。
2、truncate比delete使用锁通常较少：
delete由于是一行一行删除，所以需要对处理的行进行加锁，而且是行锁。truncate操作由于是对页操作，所以只需要申请页锁或者表锁。
3、truncate对表中的所有页都清空： 执行delete后，表还是会有空页，但是truncate则会全部清除。但是truncate会保留表结构、列、约束、索引等。
 
为了更好地删除空间，可以使用以下方法：
1、在表中创建聚集索引
2、如果所有数据已经不要，那使用truncate而不是delete
另外，对于delete操作而留下的空间，会在插入时重用。如果觉得这些空间存在不好，那么可以重建(创建)聚集索引来释放空间。

参考博客：http://blog.csdn.net/dba_huangzj/article/details/7913068