sqlserver delete与truncate的相同和不同

经常会遇到的一个问题，查了一些文档汇总一下它们的相同和不同点，后续找到了继续补充

 

一、 相同点

• 不带条件的delete与truncate均可删除表中所有数据
• delete与truncate均可回滚，这是跟oracle很不一样的地方，oracle的truncate不能回滚，pg可以
• 均不更改表结构、索引、约束等
• 均不会缩小数据文件大小，若要缩小需使用 DBCC SHRINKFILE (N'Testdb' , 40000)

 

二、 不同点

对比项	Delete	Truncate
命令类型	DML	DDL
删除特定行	可以	不可以
锁粒度及数量	在需要删除的每一行上加行锁	加SCH-M锁和页锁锁定全表
事务日志记录	每次删除一行，并在事务日志中记录每个已删除行的信息。耗时长，占用空间多	通过deallocating原先存储该表的数据页来删除表中的所有行，仅在事务日志中记录deallocating的数据页。耗时短，占用空间少
速度	大表非常慢	大表也很快
空间释放	若表有聚集索引，删除的数据空间是被设置为可用状态，其他表都可以使用。重建聚集索引也可释放数据页，但数据文件不会缩小。 ALTER INDEX PK_Testtab on Testtab  REBUILD With (FillFactor = 90 , Online= On);   若没有聚集索引，删除数据空间也会设置为可用状态，但是只能给被删除数据的表使用。必须使用排他表锁（LCK_M_X）才能释放堆中的空页 DELETE FROM <TableName> WITH (TABLOCK);	truncate可取消分配给表的所有数据页，会释放数据页，但数据文件不会缩小。
重置表identity列计数器	不会	会，重置表identity列计数器为seed值（未定义seed值则重置为1）
权限	对表有删除权限（仅有该权限无法truncate表）	Alter表权限
用于索引视图（indexed view）	可以	不可以
用于有FOREIGN KEY约束引用的表	可以	不可以（引用自身除外）
用于配置了事务复制或合并复制的表	可以	不可以
激活delete相关触发器	可以	不可以
可查看执行计划	可以	不可以

 

参考

https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15

https://support.microsoft.com/zh-cn/help/913399/space-that-a-table-uses-is-not-completely-released-after-you-use-a-del

https://www.sqlshack.com/difference-between-sql-truncate-and-sql-delete-statements-in-sql-server/

https://www.cnblogs.com/gered/p/10272434.html

https://www.cnblogs.com/lyhabc/articles/3138614.html