TRUNCATE TABLE (Transact-SQL)

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL verwaltete Instanz Azure Synapse Analytics Platform System (PDW) Warehouse in Microsoft Fabric

Entfernt alle Zeilen oder angegebenen Partitionen einer Tabelle, ohne die einzelnen Löschungen zu protokollieren. TRUNCATE TABLE ähnelt der DELETE Anweisung ohne WHERE Klausel; ist jedoch TRUNCATE TABLE schneller und verwendet weniger System- und Transaktionsprotokollressourcen.

Transact-SQL-Syntaxkonventionen

Syntax

Syntax für SQL Server und Azure SQL-Datenbank

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>

Syntax für Microsoft Fabric, Azure Synapse Analytics und Parallel Data Warehouse.

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

Argumente

database_name

Der Name der Datenbank.

schema_name

Der Name des Schemas, zu dem die Tabelle gehört.

table_name

Der Name der Tabelle, die abgeschnitten werden soll oder aus der alle Zeilen entfernt werden. table_name muss ein Literal sein. table_name kann keine Funktion oder Variable seinOBJECT_ID().

WITH ( PARTITIONS ( { <partition_number_expression> | <range> } [ , ...n ] ) )

Gilt für: SQL Server 2016 (13.x) und höhere Versionen.

Gibt die Partitionen an, die abgeschnitten oder aus denen alle Zeilen entfernt werden sollen. Wenn die Tabelle nicht partitioniert ist, generiert das WITH PARTITIONS Argument einen Fehler. Wenn die WITH PARTITIONS Klausel nicht angegeben wird, wird die gesamte Tabelle abgeschnitten.

<partition_number_expression> kann wie folgt angegeben werden:

  • Geben Sie die Nummer einer Partition an, beispielsweise: WITH (PARTITIONS (2))

  • Geben Sie die Partitionsnummern mehrerer einzelner Partitionen durch Kommas getrennt an, beispielsweise: WITH (PARTITIONS (1, 5))

  • Geben Sie sowohl Bereiche als auch einzelne Partitionen an, beispielsweise: WITH (PARTITIONS (2, 4, 6 TO 8))

  • <range> kann als Partitionsnummern angegeben werden, die durch das Wort TOgetrennt sind, z. B.: WITH (PARTITIONS (6 TO 8))

Um eine partitionierte Tabelle abzuschneiden, müssen die Tabelle und Indizes ausgerichtet werden (partitioniert auf dieselbe Partitionsfunktion).

Hinweise

Im Vergleich zur DELETE Aussage TRUNCATE TABLE hat die folgenden Vorteile:

  • Es wird weniger Speicherplatz für die Transaktionsprotokolle verwendet.

    Die DELETE Anweisung entfernt zeilenweise und zeichnet einen Eintrag im Transaktionsprotokoll für jede gelöschte Zeile auf. Beim Entfernen der Daten mit TRUNCATE TABLE werden die zur Speicherung der Tabellendaten verwendeten Datenseiten freigegeben, und nur die Freigaben der Datenseiten werden im Transaktionsprotokoll aufgezeichnet.

  • In der Regel werden weniger Sperren verwendet.

    Wenn die DELETE Anweisung mit einer Zeilensperre ausgeführt wird, wird jede Zeile in der Tabelle zum Löschen gesperrt. TRUNCATE TABLE Sperrt immer die Tabelle (einschließlich eines Schemas (SCH-M) und einer Seite, aber nicht jede Zeile.

  • Nullseiten verbleiben ausnahmslos in der Tabelle.

    Nachdem eine DELETE Anweisung ausgeführt wurde, kann die Tabelle weiterhin leere Seiten enthalten. Leere Seiten in einem Heap können z. B. nicht ohne eine exklusive (LCK_M_X) Tabellensperre abgeglichen werden. Wenn der Löschvorgang keine Tabellensperre verwendet, enthält die Tabelle (der Heap) viele leere Seiten. Bei Indizes kann der Löschvorgang leere Seiten hinter sich lassen, obwohl ein Hintergrundbereinigungsprozess diese Seiten schnell zulässt.

TRUNCATE TABLE entfernt alle Zeilen aus einer Tabelle, aber die Tabellenstruktur und die zugehörigen Spalten, Einschränkungen, Indizes usw. bleiben erhalten. Verwenden Sie die DROP TABLE-Anweisung, um neben den dazugehörigen Daten auch die Tabellendefinition zu entfernen.

Wenn die Tabelle eine Identitätsspalte enthält, wird der Zähler für diese Spalte auf den Ausgangswert zurückgesetzt, der für die Spalte definiert ist. Wenn kein Seed definiert wurde, wird der Standardwert 1 verwendet. Verwenden Sie stattdessen die Verwendung DELETE , um den Identitätszähler beizubehalten.

Ein TRUNCATE TABLE Vorgang kann innerhalb einer Transaktion zurückgesetzt werden.

Begrenzungen

Sie können nicht für Tabellen verwenden TRUNCATE TABLE , die:

  • Wird durch eine FOREIGN KEY Einschränkung referenziert. Sie können eine Tabelle abschneiden, die über einen Fremdschlüssel verfügt, der auf sich selbst verweist.

  • Die Tabelle ist an einer indizierten Sicht beteiligt.

  • Die Veröffentlichung wird mithilfe einer Transaktionsreplikation oder Mergereplikation vorgenommen.

  • Es handelt sich um eine temporale Tabelle.

  • Wird durch eine EDGE Einschränkung referenziert.

Verwenden Sie für Tabellen mit einem oder mehreren dieser Merkmale stattdessen die DELETE Anweisung.

TRUNCATE TABLE ein Trigger kann nicht aktiviert werden, da der Vorgang keine einzelnen Zeilenlöschvorgänge protokolliert. Weitere Informationen finden Sie unter CREATE TRIGGER (Transact-SQL).

In Azure Synapse Analytics und Analytics-Plattformsystem (PDW):

  • TRUNCATE TABLE ist innerhalb der EXPLAIN Anweisung nicht zulässig.

  • TRUNCATE TABLE kann nicht innerhalb einer Transaktion ausgeführt werden.

Abschneiden großer Tabellen

In Microsoft SQL Server können Tabellen gelöscht oder abgeschnitten werden, die mehr als 128 Blöcke enthalten, ohne simultane Sperren für alle Blöcke aufrechtzuerhalten, die für den Löschvorgang erforderlich sind.

Berechtigungen

Die mindestens erforderliche Berechtigung ist ALTER für table_name. TRUNCATE TABLE Berechtigungen sind standardmäßig für den Tabellenbesitzer, Die Mitglieder der festen Serverrolle "sysadmin " und die db_owner und db_ddladmin festen Datenbankrollen und können nicht übertragen werden. Sie können jedoch die TRUNCATE TABLE-Anweisung innerhalb eines Moduls einbinden, z. B. eine gespeicherte Prozedur, und mit der EXECUTE AS-Klausel für das Modul die passenden Berechtigungen erteilen.

Beispiele

A. Abschneiden einer Tabelle

Im folgenden Beispiel werden alle Daten aus der JobCandidate-Tabelle entfernt. SELECT-Anweisungen werden vor und nach der TRUNCATE TABLE-Anweisung eingeschlossen, um die Ergebnisse zu vergleichen.

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. Abschneiden von Tabellenpartitionen

Gilt für: SQL Server 2016 (13.x) und höhere Versionen.

Im folgende Beispiel werden die angegebenen Partitionen einer partitionierten Tabelle abgeschnitten. Mit der Syntax WITH (PARTITIONS (2, 4, 6 TO 8)) werden die Partitionen 2, 4, 6, 7 und 8 abgeschnitten.

TRUNCATE TABLE PartitionTable1
WITH (PARTITIONS (2, 4, 6 TO 8));
GO

C. Zurücksetzen eines Abkürzungsvorgangs

Das folgende Beispiel zeigt, dass ein TRUNCATE TABLE Vorgang innerhalb einer Transaktion zurückgesetzt werden kann.

  1. Erstellen Sie eine Testtabelle mit drei Zeilen.

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

  2. Überprüfen Sie die Daten vor dem Abschneiden.

    SELECT * FROM TruncateTest;
GO

  3. Kürzen Sie die Tabelle innerhalb einer Transaktion, und überprüfen Sie die Anzahl der Zeilen.

    BEGIN TRANSACTION;

TRUNCATE TABLE TruncateTest;

SELECT * FROM TruncateTest;

    Sie sehen, dass die Tabelle leer ist.

  4. Führen Sie ein Rollback der Transaktion durch, und überprüfen Sie die Daten.

    ROLLBACK TRANSACTION;
GO

SELECT * FROM TruncateTest;
GO

    Alle drei Zeilen werden angezeigt.

  5. Bereinigen Sie die Tabelle.

    DROP TABLE TruncateTest;
GO

Zugehöriger Inhalt