TRUNCATE TABLE (Transact-SQL)

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

從資料表中移除所有資料列或資料庫的指定資料分割，而不需記錄個別資料列刪除。 TRUNCATE TABLE與沒有 WHERE 子句的DELETE語句類似，不過，TRUNCATE TABLE速度較快，而且使用較少的系統和事務歷史記錄資源。

Transact-SQL 語法慣例

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 可以復原交易內的作業。

  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

相關內容