TRUNCATE TABLE (Transact-SQL)

適用対象: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric のハードウェア SQL データベース

個々の行の削除をログに記録せず、テーブルまたはテーブルの指定したパーティションからすべての行を削除します。 TRUNCATE TABLEは、WHERE句のないDELETE ステートメントに似ていますが、TRUNCATE TABLEは高速であり、使用するシステム ログ リソースとトランザクション ログ リソースが少なくなります。

Transact-SQL 構文表記規則

構文

SQL Server、Azure SQL Database、Fabric 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、Parallel Data Warehouse の構文。

TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

引数

database_name

データベースの名前。

schema_name

テーブルが属しているスキーマの名前です。

table_name

切り捨てるテーブルの名前、またはすべての行が削除されるテーブルの名前。 table_name はリテラルで指定する必要があります。 table_nameOBJECT_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 ステートメントは、一度に 1 行ずつ行を削除し、削除された行ごとにトランザクション ログにエントリを記録します。 TRUNCATE TABLE は、テーブル データを格納するのに使用するデータ ページの割り当てを解除することによってデータを削除し、ページの割り当ての解除だけをトランザクション ログに記録します。

• 通常、使用されるロックの数が削減されます。

  DELETE ステートメントが行ロックを使用して実行されると、テーブル内の各行が削除のためにロックされます。 TRUNCATE TABLE 常にテーブル (スキーマ (SCH-M) ロックを含む) とページがロックされますが、各行はロックされません。

• テーブル内にページは一切残されません。

  DELETEステートメントが実行された後も、テーブルには空のページを含めることができます。 たとえば、ヒープ内の空のページは、少なくとも排他 (LCK_M_X) テーブル ロックなしでは割り当てを解除できません。 削除操作がテーブル ロックを使用しない場合は、テーブル (ヒープ) には多数の空のページが含まれます。 インデックスの場合、削除操作では空のページが残ることがありますが、バックグラウンド クリーンアップ プロセスではこれらのページの割り当てが迅速に解除されます。

TRUNCATE TABLE はテーブルからすべての行を削除しますが、テーブル構造とその列、制約、インデックスなどが残ります。 テーブルのデータとテーブル定義を削除する場合は、DROP TABLE ステートメントを使用します。

テーブルに ID 列が含まれている場合は、その列に対するカウンターは、その列に対して定義されたシード値にリセットされます。 シードが定義されていない場合は、 1 既定値が使用されます。 ID カウンターを保持するには、代わりに DELETE を使用します。

TRUNCATE TABLE操作は、トランザクション内でロールバックできます。

Fabric SQL データベースでは、テーブルを切り捨てると、そのテーブルの Fabric OneLake からミラー化されたすべてのデータが削除されます。

制限事項

次のテーブルで TRUNCATE TABLE を使用することはできません。

• FOREIGN KEY制約によって参照されます。 それ自体を参照する外部キーを持つテーブルは切り捨てることができます。

• インデックス付きビューで使用されているテーブル。

• トランザクション レプリケーションとマージ レプリケーションを使用してパブリッシュされているテーブル。

• システムのバージョン情報のテンポラル テーブル。

• EDGE制約によって参照されます。

これらの特性の 1 つ以上を持つテーブルの場合は、代わりに 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アクセス許可は、既定ではテーブル所有者、sysadmin、固定サーバー ロール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. 3 行のテスト テーブルを作成します。

   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
   

   3 つの行がすべて表示されます。

5. テーブルをクリーンアップします。

   DROP TABLE TruncateTest;
   GO
   

関連するコンテンツ

• DELETE (Transact-SQL)
• DROP TABLE (Transact-SQL)
• CREATE TABLE (Transact-SQL) IDENTITY (プロパティ)