TRUNCATE TABLE (Transact-SQL)
適用於:Microsoft Fabric 中的 SQL Server
Azure SQL 資料庫 Azure SQL 受控執行個體
Azure Synapse Analytics
Analytics Platform System (PDW) 倉儲
從資料表中移除所有資料列或資料庫的指定資料分割,而不需記錄個別資料列刪除。 TRUNCATE TABLE
與沒有 WHERE
子句的DELETE
語句類似,不過,TRUNCATE TABLE
速度較快,而且使用較少的系統和事務歷史記錄資源。
Syntax
SQL Server 和 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>
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
許可權預設為數據表擁有者、系統管理員固定伺服器角色的成員,以及 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