次の方法で共有

トランケートテーブル(Transact-SQL)

See More

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric のウェアハウスMicrosoft Fabric の SQL データベース

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

Transact-SQL 構文表記規則

Syntax

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 }
[ ; ]

Arguments

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))

パーティション テーブルの切り捨てを行うには、テーブルとインデックスが連携している (同じパーティション関数でパーティション分割されている) 必要があります。

Remarks

DELETEステートメントと比較すると、TRUNCATE TABLEには次の利点があります。

  • トランザクション ログが使用する領域が削減されます。

    DELETE ステートメントは、一度に 1 行ずつ行を削除し、削除された行ごとにトランザクション ログにエントリを記録します。 TRUNCATE TABLE は、テーブルとインデックス データの格納に使用するデータ ページの割り当てを解除し、トランザクション ログに割り当て解除されたページのみを記録することで、データを削除します。

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

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

  • 例外なく、テーブルまたはそのインデックスには 0 ページが残ります。

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

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

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

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

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

ディファード・ディスロケーション

128 エクステント以上を使用するテーブルが切り捨てられると、データベース エンジンは、トランザクションのコミット後まで、実際のページの割り当て解除とそれに関連付けられているロックを延期します。 切り捨ては、論理と物理の 2 つのフェーズで発生します。 論理フェーズでは、テーブルとそのインデックスによって使用される既存の割り当て単位が割り当て解除用にマークされ、トランザクションがコミットされるまでロックされます。 物理フェーズでは、バックグラウンド プロセスによって割り当て解除のマークが付けられたページが削除されます。 つまり、 TRUNCATE TABLE によって解放された領域は、新しい割り当てにすぐに使用できない可能性があります。

高速データベース・リカバリーが有効になっている場合、切り捨てではエクステントの数に関係なく、個別の論理フェーズと物理フェーズが使用されます。

Limitations

次のテーブルで 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 はトランザクション内で実行できません。

Permissions

ALTER で必要な最小限の権限は です。 TRUNCATE TABLE アクセス許可は、既定ではテーブル所有者、 sysadmin 固定サーバー ロールのメンバー、固定データベース ロールの db_owner と db_ddladmin であり、転送できません。 ただし、TRUNCATE TABLE ステートメントをストアド プロシージャなどのモジュール内に組み込み、EXECUTE AS 句を使用してそのモジュールに適切な権限を与えることはできます。

Examples

A. テーブルを切り捨てる

次の例では、JobCandidate テーブルからすべてのデータを削除しています。 SELECT ステートメントを TRUNCATE TABLE ステートメントの前後に挿入して結果を比較しています。

USE AdventureWorks2022;

SELECT COUNT(*) AS BeforeTruncateCount
FROM HumanResources.JobCandidate;

TRUNCATE TABLE HumanResources.JobCandidate;

SELECT COUNT(*) AS AfterTruncateCount
FROM HumanResources.JobCandidate;

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];

CREATE TABLE TruncateTest (ID INT IDENTITY (1, 1) NOT NULL);
GO
INSERT INTO TruncateTest DEFAULT VALUES;
GO 3

  2. 切り捨てる前にデータを確認します。

    SELECT ID FROM TruncateTest;

  3. トランザクション内のテーブルを切り捨て、行数を確認します。

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT ID FROM TruncateTest;

    テーブルが空であることがわかります。

  4. トランザクションをロールバックし、データを確認します。

    ROLLBACK TRANSACTION;

SELECT ID FROM TruncateTest;

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

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

    DROP TABLE TruncateTest;

関連コンテンツ

  • Last updated on