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使用较少的系统和事务日志资源。

Transact-SQL 语法约定

语法

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 }
[ ; ]

参数

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 回滚事务中的操作。

1. 创建包含三行的测试表。

   USE [tempdb];
   GO
   CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL);
   GO
   INSERT INTO TruncateTest DEFAULT VALUES;
   GO 3
   

2. 在截断之前检查数据。

   SELECT * FROM TruncateTest;
   GO
   

3. 截断事务中的表，并检查行数。

   BEGIN TRANSACTION;
   
   TRUNCATE TABLE TruncateTest;
   
   SELECT * FROM TruncateTest;
   

   可以看到该表为空。

4. 回滚事务并检查数据。

   ROLLBACK TRANSACTION;
   GO
   
   SELECT * FROM TruncateTest;
   GO
   

   可以看到所有三行。

5. 清理表。

   DROP TABLE TruncateTest;
   GO
   

相关内容

• DELETE (Transact-SQL)
• DROP TABLE (Transact-SQL)
• CREATE TABLE (Transact-SQL) IDENTITY (Property)