经常会遇到的一个问题,查了一些文档汇总一下它们的相同和不同点,后续找到了继续补充
一、 相同点
- 不带条件的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://www.sqlshack.com/difference-between-sql-truncate-and-sql-delete-statements-in-sql-server/