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