Delete和Truncate的区别

原文: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

结果:

Delete和Truncate的区别

sp_helpindex '[Sales].SalesOrderDetail_J';
GO

结果:

Delete和Truncate的区别

sp_helpindex '[Sales].SalesOrderDetail_F'

结果:

Delete和Truncate的区别

然后,用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')

结果如下:

Delete和Truncate的区别

从上图可以看出,堆表(即没有聚集索引的表)扫描出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

结果:

Delete和Truncate的区别

sp_helpindex '[Sales].SalesOrderDetail_J';

go

结果:

Delete和Truncate的区别

sp_helpindex '[Sales].SalesOrderDetail_F'

结果:

Delete和Truncate的区别

现在进行清空操作:

TRUNCATE TABLE [Sales].SalesOrderDetail_D

GO

TRUNCATE TABLE [Sales].SalesOrderDetail_J

go

TRUNCATE TABLE [Sales].SalesOrderDetail_F

再检查数据分布情况:

Delete和Truncate的区别

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

通过上面的对比,可以得出以下结论:

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

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

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

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

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

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

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

(1)、在表中创建聚集索引

(2)、如果所有数据已经不要,那使用TRUNCATE
而不是DELETE,删除后DROP TABLE

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

上一篇:Requests:Python HTTP Module学习笔记(一)(转)


下一篇:cocos2d-x游戏开发系列教程-超级玛丽06-CMGameScene