SQL SERVER-Delete和Truncate的区别

来源:http://www.sh-fengwen.com 作者:气血双补 人气:102 发布时间:2019-11-19
摘要:本文导读:删除表中的数据的方法有delete,truncate, 其中TRUNCATETABLE用于删除表中的所有行,而不记录单个行删除操作。TRUNCATE TABLE 与没有WHERE 子句的 DELETE 语句类似;但是,TRUNCATE TABLE速度

本文导读:删除表中的数据的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行,而不记录单个行删除操作。TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似;但是,TRUNCATE TABLE 速度更快,使用的系统资源和事务日志资源更少。下面介绍SQL中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操作而留下的空间,会在插入时重用。如果觉得这些空间存在不好,那么可以重建(创建)聚集索引来释放空间。

 

参考博客:

当你不再需要该表时, 用 drop;当你仍要保留该表,但要删除所有记录时, 用 truncate;当你要删除部分记录时(always with a WHERE clause), 用 delete.

 

Truncate是一个能够快速清空资料表内所有资料的SQL语法。并且能针对具有自动递增值的字段,做计数重置归零重新计算的作用。

 

一、Truncate语法

[ { database_name.[ schema_name ]. | schema_name . } ]
    table_name
[ ; ]  

参数

database_name
数据库的名称。

schema_name
表所属架构的名称。

table_name
要截断的表的名称,或要删除其全部行的表的名称。

 

二、Truncate使用注意事项

 

1、TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。

 

2、DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

 

3、TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。

 

4、对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。

 

5、TRUNCATE TABLE 不能用于参与了索引视图的表。

 

6、对用TRUNCATE TABLE删除数据的表上增加数据时,要使用UPDATE STATISTICS来维护索引信息。

本文由美高梅游戏平台网站发布于气血双补,转载请注明出处:SQL SERVER-Delete和Truncate的区别

关键词:

上一篇:SQL SERVER-Delete和Truncate的区别

下一篇:没有了

最火资讯