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

適用於: 是SQL Server (從 2008 開始) 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server (starting with 2008) 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 Server (SQL 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:

  • EXPLAIN 陳述式內不允許 TRUNCATE TABLE。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 Server (SQL 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)