一般对于没有用的数据,都会经行删除,而删除通常使用的是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操作而留下的空间,会在插入时重用。如果觉得这些空间存在不好,那么可以重建/创建聚集索引来释放空间。