Delete和Truncate的区别

 

一般对于没有用的数据，都会经行删除，而删除通常使用的是DELETE和TRUNCATE命令。对于有条件地删除，基本上就会使用DELETE，当然还是没有绝对，用TRUNCATE也可以实现，只要把【不需要】删除的数据插入新表，然后truncate源表，再把数据导回来或者直接重命名新表就可以了。

下面例子主要比较全表删除的情况下DELETE 和TRUNCATE 之间的差异：

 

首先，先创建测试用例：本例使用AdventureWorks数据库。先创建3个表：

--堆，即没有聚集索引
SELECT * INTO Sales.SalesOrderDetail_D FROM  Sales.SalesOrderDetail

--有聚集索引
SELECT * INTO Sales.SalesOrderDetail_J FROM  Sales.SalesOrderDetail 

CREATE CLUSTERED INDEX Clustered_SalesOrderDetail_J ON Sales.SalesOrderDetail_J (SalesOrderID,SalesOrderDetailID)
GO
--没有聚集索引，但有非聚集索引
SELECT * INTO Sales.SalesOrderDetail_F FROM  Sales.SalesOrderDetail

CREATE NONCLUSTERED INDEX Nonclustered_SalesOrderDetail_F ON Sales.SalesOrderDetail_F (SalesOrderID,SalesOrderDetailID)
GO

 

查看一下各个表的索引情况：

sp_helpindex '[Sales].SalesOrderDetail_D';
GO

 

结果：

 

sp_helpindex '[Sales].SalesOrderDetail_J';
GO

 

结果：

 

sp_helpindex '[Sales].SalesOrderDetail_F'

 

结果：

 

然后，用DELETE对三个表进行清空操作：

DELETE TABLE [Sales].SalesOrderDetail_D

GO

DELETE TABLE [Sales].SalesOrderDetail_J

go

DELETE TABLE [Sales].SalesOrderDetail_F

 

 

使用DBCC SHOWCONTIG命令来查看数据分布情况：

DBCC SHOWCONTIG( '[Sales].SalesOrderDetail_D')

GO

DBCC SHOWCONTIG('[Sales].SalesOrderDetail_J')

go

DBCC SHOWCONTIG('[Sales].SalesOrderDetail_F')

 

结果如下：

从上图可以看出，堆表（即没有聚集索引的表）扫描出82个页和11个区，由于已经删除属于，所以这些都是空的。而有聚集索引的表，只有1个页和1个区。有非聚集索引的表，也有66个页和9个区。

可以看到，没有聚集索引的表删除数据后还遗留了不少空间。

 

 

下面来看看TRUNCATE操作：

同样，先创建表，使用上面的建表语句创建同样的表，以保证对比一致性：

DROP TABLE Sales.SalesOrderDetail_D

GO

DROP TABLE Sales.SalesOrderDetail_J

GO

DROP TABLE Sales.SalesOrderDetail_F

GO

 

--堆，即没有聚集索引

SELECT * INTO Sales.SalesOrderDetail_D FROM  Sales.SalesOrderDetail

 

--有聚集索引

SELECT * INTO Sales.SalesOrderDetail_J FROM  Sales.SalesOrderDetail 

 

CREATE CLUSTERED INDEX Clustered_SalesOrderDetail_J ON Sales.SalesOrderDetail_J(SalesOrderID,SalesOrderDetailID)

GO

--没有聚集索引，但有非聚集索引

SELECT * INTO Sales.SalesOrderDetail_F FROM  Sales.SalesOrderDetail

 

CREATE NONCLUSTERED INDEX Nonclustered_SalesOrderDetail_F ON Sales.SalesOrderDetail_F(SalesOrderID,SalesOrderDetailID)

GO

 

然后查看相关索引：

sp_helpindex '[Sales].SalesOrderDetail_D';

GO

结果：

 

sp_helpindex '[Sales].SalesOrderDetail_J';

go

结果：

 

sp_helpindex '[Sales].SalesOrderDetail_F'

结果：

 

现在进行清空操作：

TRUNCATE TABLE [Sales].SalesOrderDetail_D

GO

TRUNCATE TABLE [Sales].SalesOrderDetail_J

go

TRUNCATE TABLE [Sales].SalesOrderDetail_F

 

再检查数据分布情况：

可以看到，3个表都已经没有页和区了。

 

通过上面的对比，可以得出以下结论：

1、  Truncate比Delete所用的事务日志空间更少：

DELETE 是一行一行操作，并且把记录都存进日志文件（说明一下，无论任何恢复模式，都会记录日志）。而TRUNCATE操作，是对一个页操作，在日志中，仅仅记录释放页面的这个动作，而不记录每一行。

2、  Truncate比Delete使用锁通常较少：

DELETE由于是一行一行删除，所以需要对处理的行进行加锁，而且是行锁。TRUNCATE操作由于是对页操作，所以只需要申请页锁或者表锁。

3、  TRUNCATE对表中的所有页都清空：

执行DELETE后，表还是会有空页，但是TRUNCATE则会全部清除。但是TRUNCATE会保留表结构、列、约束、索引等。而DELETE之后，会哦他能够过后台清除空页。

 

为了更好地删除空间，可以使用以下方法：

（1）、在表中创建聚集索引

（2）、如果所有数据已经不要，那使用TRUNCATE 而不是DELETE，删除后DROP TABLE 。

 

另外，对于由于DELETE操作而留下的空间，会在插入时重用。如果觉得这些空间存在不好，那么可以重建/创建聚集索引来释放空间。