TRUNCATE TABLE (Transact-SQL)
适用于:Microsoft Fabric 中的 SQL Server
Azure SQL 数据库
Azure SQL 托管实例
Azure Synapse Analytics Analytics
Platform System (PDW) Warehouse
删除表中的所有行或表中指定的分区,不记录单个行删除操作。 TRUNCATE TABLE
与没有WHERE
子句的DELETE
语句类似;但是,速度更快,TRUNCATE TABLE
使用较少的系统和事务日志资源。
语法
SQL Server 和 Azure SQL 数据库 的语法。
TRUNCATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name | table_name }
[ WITH ( PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ) ]
[ ; ]
<range> ::=
<partition_number_expression> TO <partition_number_expression>
Microsoft Fabric、Azure Synapse Analytics 和并行数据仓库的语法。
TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]
注意
若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法,请参阅早期版本文档。
参数
database_name
数据库的名称。
schema_name
表所属的架构的名称。
table_name
要截断或从中删除所有行的表的名称。 table_name 须是文本。 table_name 不能是 OBJECT_ID()
函数或变量。
WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )
适用于:SQL Server 2016 (13.x) 及更高版本。
指定要截断或删除其中所有行的分区。 如果未对表进行分区,则 WITH PARTITIONS
参数将生成错误。 如果未提供子 WITH PARTITIONS
句,将截断整个表。
可以通过下列方式指定 <partition_number_expression>
:
提供分区号,例如:
WITH (PARTITIONS (2))
提供若干单独分区的分区号,并用逗号分隔,例如:
WITH (PARTITIONS (1, 5))
同时提供范围和单独分区,例如:
WITH (PARTITIONS (2, 4, 6 TO 8))
<range>
可以指定为用单词TO
分隔的分区号,例如:WITH (PARTITIONS (6 TO 8))
要截断一个已分区表,表和索引必须对齐(在同一个分区函数上进行分区)。
注解
与 DELETE
语句相比, TRUNCATE TABLE
具有以下优势:
所用的事务日志空间较少。
该
DELETE
语句一次删除一行,并记录每个已删除行的事务日志中的条目。TRUNCATE TABLE
通过释放用于存储表数据的数据页删除数据,且仅在事务日志中记录页释放。使用的锁通常较少。
DELETE
使用行锁执行该语句时,表中的每一行都会被锁定以删除。TRUNCATE TABLE
始终锁定表(包括架构(SCH-M
)锁和页,但不会锁定每一行。如无例外,在表中不会留有任何页。
DELETE
执行语句后,表仍可以包含空页。 例如,在没有至少具有独占表LCK_M_X
锁的情况下,堆中的空页无法解除分配。 如果执行删除操作时未使用表锁,表(堆)中将包含许多空页。 对于索引,删除操作可能会让空页隐藏,尽管后台清理过程会快速解除分配这些页面。
TRUNCATE TABLE
从表中删除所有行,但表结构及其列、约束、索引等仍保留。 若要删除表定义及其数据,请使用 DROP TABLE
语句。
如果表包含标识列,该列的计数器重置为该列定义的种子值。 如果未定义种子,则使用默认值 1
。 若要保留标识计数器,请改用 DELETE
。
TRUNCATE TABLE
可以在事务中回滚操作。
限制
不能在以下表上使用 TRUNCATE TABLE
:
由
FOREIGN KEY
约束引用。 可以截断具有引用自身的外键的表。参与索引视图的表。
通过使用事务复制或合并复制发布的表。
系统版本控制时态表。
由
EDGE
约束引用。
对于具有一个或多个这些特征的表,请改用 DELETE
该语句。
TRUNCATE TABLE
无法激活触发器,因为该操作不会记录单个行删除。 有关详细信息,请参阅 CREATE TRIGGER (Transact-SQL)。
在 Azure Synapse Analytics 和 Analytics Platform System (PDW) 中:
TRUNCATE TABLE
不允许在EXPLAIN
语句中。TRUNCATE TABLE
不能在事务内部运行。
截断大型表
Microsoft 和 SQL Server 能够删除或截断超过 128 个区的表,而无需同步锁定所有需删除的区。
权限
所需的最低权限是 table_name 上的 ALTER
权限。 TRUNCATE TABLE
权限默认为表所有者、sysadmin 固定服务器角色的成员以及db_owner
固定数据库角色和db_ddladmin固定数据库角色的成员,并且不可传输。 但是,可以在诸如存储过程这样的模块中加入 TRUNCATE TABLE
语句,然后为使用 EXECUTE AS
子句的模块授予适当的权限。
示例
A. 截断表
下面的示例删除 JobCandidate
表中的所有数据。 在 SELECT
语句之前和之后使用 TRUNCATE TABLE
语句来比较结果。
USE AdventureWorks2022;
GO
SELECT COUNT(*) AS BeforeTruncateCount
FROM HumanResources.JobCandidate;
GO
TRUNCATE TABLE HumanResources.JobCandidate;
GO
SELECT COUNT(*) AS AfterTruncateCount
FROM HumanResources.JobCandidate;
GO
B. 截断表分区
适用于:SQL Server 2016 (13.x) 及更高版本。
下面的示例将截断已分区表的指定分区。 WITH (PARTITIONS (2, 4, 6 TO 8))
语法导致分区号 2、4、6、7 和 8 被截断。
TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO
°C 回滚截断操作
以下示例演示如何 TRUNCATE TABLE
回滚事务中的操作。
创建包含三行的测试表。
USE [tempdb]; GO CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL); GO INSERT INTO TruncateTest DEFAULT VALUES; GO 3
在截断之前检查数据。
SELECT * FROM TruncateTest; GO
截断事务中的表,并检查行数。
BEGIN TRANSACTION; TRUNCATE TABLE TruncateTest; SELECT * FROM TruncateTest;
可以看到该表为空。
回滚事务并检查数据。
ROLLBACK TRANSACTION; GO SELECT * FROM TruncateTest; GO
可以看到所有三行。
清理表。
DROP TABLE TruncateTest; GO