TRUNCATE TABLE (Transact-SQL)
適用於: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>
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
相關內容
意見反映
https://aka.ms/ContentUserFeedback。
即將推出:我們會在 2024 年淘汰 GitHub 問題,並以全新的意見反應系統取代並作為內容意見反應的渠道。 如需更多資訊,請參閱:提交及檢視以下的意見反映: