TRUNCATE TABLE (Transact-SQL)TRUNCATE TABLE (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

删除表中的所有行或表中指定的分区,不记录单个行删除操作。Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似;但是,TRUNCATE TABLE 速度更快,使用的系统资源和事务日志资源更少。TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
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>  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]  

参数Arguments

database_namedatabase_name
数据库的名称。Is the name of the database.

schema_nameschema_name
表所属架构的名称。Is the name of the schema to which the table belongs.

table_nametable_name
要截断的表的名称,或要删除其全部行的表的名称。Is the name of the table to truncate or from which all rows are removed. table_name 须是文本。table_name must be a literal. table_name 不能是 OBJECT_ID() 函数或变量。table_name cannot be the OBJECT_ID() function or a variable.

WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )
适用范围:SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)当前版本Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version)

指定要截断或删除其中所有行的分区。Specifies the partitions to truncate or from which all rows are removed. 如果表未分区,则 WITH PARTITIONS 参数会生成错误。If the table is not partitioned, the WITH PARTITIONS argument will generate an error. 如果未提供 WITH PARTITIONS 子句,则整个表都将被截断。If the WITH PARTITIONS clause is not provided, the entire table will be truncated.

可以按以下方式指定 <partition_number_expression>:<partition_number_expression> can be specified in the following ways:

  • 提供分区号,例如:WITH (PARTITIONS (2))Provide the number of a partition, for example: WITH (PARTITIONS (2))

  • 提供若干单独分区的分区号,并用逗号分隔,例如:WITH (PARTITIONS (1, 5))Provide the partition numbers for several individual partitions separated by commas, for example: WITH (PARTITIONS (1, 5))

  • 同时提供范围和单独分区,例如:WITH (PARTITIONS (2, 4, 6 TO 8))Provide both ranges and individual partitions, for example: WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> 可指定为由单词 TO 隔开的分区号,例如:WITH (PARTITIONS (6 TO 8))<range> can be specified as partition numbers separated by the word TO, for example: WITH (PARTITIONS (6 TO 8))

要截断一个已分区表,表和索引必须对齐(在同一个分区函数上进行分区)。To truncate a partitioned table, the table and indexes must be aligned (partitioned on the same partition function).

RemarksRemarks

与 DELETE 语句相比,TRUNCATE TABLE 具有以下优点:Compared to the DELETE statement, TRUNCATE TABLE has the following advantages:

  • 所用的事务日志空间较少。Less transaction log space is used.

    DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一个项。The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE 通过释放用于存储表数据的数据页删除数据,且仅在事务日志中记录页释放。TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

  • 使用的锁通常较少。Fewer locks are typically used.

    当使用行锁执行 DELETE 语句时,将锁定表中各行以便删除。When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE 始终锁定表(包含架构 (SCH-M) 锁)和页,而不是锁定各行。TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

  • 如无例外,在表中不会留有任何页。Without exception, zero pages are left in the table.

    执行 DELETE 语句后,表仍会包含空页。After a DELETE statement is executed, the table can still contain empty pages. 例如,必须至少使用一个排他 (LCK_M_X) 表锁,才能释放堆中的空表。For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. 如果执行删除操作时没有使用表锁,表(堆)中将包含许多空页。If the delete operation does not use a table lock, the table (heap) will contain many empty pages. 对于索引,删除操作会留下一些空页,尽管这些页会通过后台清除进程迅速释放。For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. 若要删除表定义及其数据,请使用 DROP TABLE 语句。To remove the table definition in addition to its data, use the DROP TABLE statement.

如果表包含标识列,该列的计数器重置为该列定义的种子值。If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. 如果未定义种子,则使用默认值 1。If no seed was defined, the default value 1 is used. 若要保留标识计数器,请使用 DELETE。To retain the identity counter, use DELETE instead.

限制Restrictions

不能对以下表使用 TRUNCATE TABLE:You cannot use TRUNCATE TABLE on tables that:

  • 由 FOREIGN KEY 约束引用的表。Are referenced by a FOREIGN KEY constraint. (您可以截断具有引用自身的外键的表。)(You can truncate a table that has a foreign key that references itself.)

  • 参与索引视图的表。Participate in an indexed view.

  • 通过使用事务复制或合并复制发布的表。Are published by using transactional replication or merge replication.

对于具有以上一个或多个特征的表,请使用 DELETE 语句。For tables with one or more of these characteristics, use the DELETE statement instead.

TRUNCATE TABLE 不能激活触发器,因为该操作不记录各个行删除。TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. 有关详细信息,请参阅 CREATE TRIGGER (Transact-SQL)For more information, see CREATE TRIGGER (Transact-SQL).

Azure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data Warehouse 中:In Azure SQL 数据仓库Azure SQL Data Warehouse and 并行数据仓库Parallel Data Warehouse:

  • TRUNCATE TABLE 不可出现在 EXPLAIN 语句中。TRUNCATE TABLE is not allowed within the EXPLAIN statement.

  • TRUNCATE TABLE 不可在事务内部运行。TRUNCATE TABLE cannot be ran inside of a transaction.

截断大型表Truncating Large Tables

MicrosoftMicrosoft SQL ServerSQL Server 能够删除或截断超过 128 个区的表,而无需同步锁定所有需删除的区。SQL ServerSQL Server has the ability to drop or truncate tables that have more than 128 extents without holding simultaneous locks on all the extents required for the drop.

权限Permissions

所需的最低权限是 table_name 上的 ALTER 权限。The minimum permission required is ALTER on table_name. TRUNCATE TABLE 权限默认授予表所有者、sysadmin 固定服务器角色的成员、db_owner 和 db_ddladmin 固定数据库角色的成员,并且不可转移权限。TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. 但是,可以在诸如存储过程这样的模块中加入 TRUNCATE TABLE 语句,然后为使用 EXECUTE AS 子句的模块授予适当的权限。However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.

示例Examples

A.A. 截断表Truncate a Table

下面的示例删除 JobCandidate 表中的所有数据。The following example removes all data from the JobCandidate table. SELECT 语句之前和之后使用 TRUNCATE TABLE 语句来比较结果。SELECT statements are included before and after the TRUNCATE TABLE statement to compare results.

USE AdventureWorks2012;  
GO  
SELECT COUNT(*) AS BeforeTruncateCount   
FROM HumanResources.JobCandidate;  
GO  
TRUNCATE TABLE HumanResources.JobCandidate;  
GO  
SELECT COUNT(*) AS AfterTruncateCount   
FROM HumanResources.JobCandidate;  
GO  

B.B. 截断表分区Truncate Table Partitions

适用范围:SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)当前版本Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version)

下面的示例将截断已分区表的指定分区。The following example truncates specified partitions of a partitioned table. WITH (PARTITIONS (2, 4, 6 TO 8)) 语法导致分区号 2、4、6、7 和 8 被截断。The WITH (PARTITIONS (2, 4, 6 TO 8)) syntax causes partition numbers 2, 4, 6, 7, and 8 to be truncated.

TRUNCATE TABLE PartitionTable1   
WITH (PARTITIONS (2, 4, 6 TO 8));  
GO  

另请参阅See Also

DELETE (Transact-SQL) DELETE (Transact-SQL)
DROP TABLE (Transact-SQL) DROP TABLE (Transact-SQL)
IDENTITY(属性)(Transact-SQL)IDENTITY (Property) (Transact-SQL)