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_1
FROM [DW_HQ].[dbo].[Fact_Sale] AS a WITH(NOLOCK)
GO

SELECT TOP 1000
    *
INTO [dbo].Fact_Sale_2
FROM [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])
GO


SELECT TOP 1000
    *
INTO [dbo].Fact_Sale_3
FROM [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_keys
CLUS_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_keys
NONCLUS_IX_Fact_Sale_3_Datekey    nonclustered located on PRIMARY    Datekey*/
接着我们使用delete命令删除这个三个表的数据
DELETE FROM [dbo].[Fact_Sale_1]
GO
DELETE FROM [dbo].[Fact_Sale_2]
GO
DELETE 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