TRUNCATE TABLE (Transact-SQL)

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

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

Transact-SQL 構文表記規則

構文

SQL Server および Azure SQL データベース の構文

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 と Parallel Data Warehouse の構文。

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

Note

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

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

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

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

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

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

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

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

制限事項

次のテーブルでは使用 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

関連するコンテンツ