TRUNCATE TABLE (Transact-SQL)

  • Artikel

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Entfernt alle Zeilen oder angegebenen Partitionen einer Tabelle, ohne die einzelnen Löschungen zu protokollieren. TRUNCATE TABLE entspricht DELETE ohne WHERE-Klausel. TRUNCATE TABLE ist jedoch schneller und verwendet weniger Systemressourcen und Ressourcen für die Transaktionsprotokollierung.

Transact-SQL-Syntaxkonventionen

Syntax

-- Syntax for SQL Server and 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>  

-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  
  
TRUNCATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]

Hinweis

Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 oder früher finden Sie unter Dokumentation zu früheren Versionen.

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 gekürzt werden soll oder aus der alle Zeilen entfernt werden. table_name muss ein Literal sein. table_name kann nicht die OBJECT_ID() -Funktion oder eine Variable sein.

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

Gilt für: SQL Server (SQL Server 2016 (13.x) bis zur aktuellen Version)

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

<partition_number_expression> kann auf die folgenden Weisen 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))

  • Für <range> können durch das Wort TO getrennte Partitionsnummern angegeben werden, beispielsweise WITH (PARTITIONS (6 TO 8)).

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

Bemerkungen

TRUNCATE TABLE bietet im Vergleich zur DELETE-Anweisung die folgenden Vorteile:

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

    Die DELETE-Anweisung entfernt jede Zeile einzeln und protokolliert jede Löschung einzeln im Transaktionsprotokoll. 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 mithilfe einer Zeilensperre ausgeführt wird, wird jede Zeile in der Tabelle zum Löschen gesperrt. Durch TRUNCATE TABLE werden immer die Tabelle (einschließlich einer Schemasperre (SCH-M)) und die Seite, aber nicht die einzelnen Zeilen gesperrt.

  • Nullseiten verbleiben ausnahmslos in der Tabelle.

    Nach dem Ausführen einer DELETE-Anweisung kann die Tabelle weiterhin leere Seiten enthalten. Die Zuordnung von leeren Seiten in einem Heap kann z. B. nur aufgehoben werden, wenn mindestens eine exklusive (LCK_M_X) Tabellensperre vorhanden ist. Wenn der Löschvorgang keine Tabellensperre verwendet, enthält die Tabelle (der Heap) viele leere Seiten. In Bezug auf Indizes können durch den Löschvorgang leere Seiten verbleiben, obwohl die Zuordnung dieser Seiten durch einen Cleanupprozess im Hintergrund schnell aufgehoben wird.

TRUNCATE TABLE entfernt alle Zeilen aus einer Tabelle, die Tabellenstruktur (Spalten, Einschränkungen, Indizes usw.) dagegen bleibt 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 Ausgangswert definiert wurde, wird der Standardwert 1 verwendet. Falls Sie den Wert des Identitätszählers erhalten möchten, verwenden Sie stattdessen DELETE.

Hinweis

Für einen TRUNCATE TABLE-Vorgang kann ein Rollback ausgeführt werden.

Beschränkungen

TRUNCATE TABLE kann nicht in Tabellen verwendet werden, für die Folgendes gilt:

  • Auf die Tabelle wird mit einer FOREIGN KEY-Einschränkung verwiesen. 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.

  • Auf die Tabelle wird durch eine EDGE-Einschränkung verwiesen.

Verwenden Sie für Tabellen, die ein oder mehrere dieser Eigenschaften aufweisen, stattdessen die DELETE-Anweisung.

TRUNCATE TABLE kann keinen Trigger aktivieren, da der Vorgang keine einzelnen Zeilenlöschungen protokolliert. Weitere Informationen finden Sie unter CREATE TRIGGER (Transact-SQL).

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

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

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

Abschneiden von großen 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 werden standardmäßig dem Tabellenbesitzer, den Mitgliedern der festen Serverrolle sysadmin sowie den Mitgliedern der festen Datenbankrollen db_owner und db_ddladmin erteilt. Diese Berechtigungen sind nicht übertragbar. 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. Tabellenpartition abschneiden

Gilt für: SQL Server (SQL Server 2016 (13.x) bis zur aktuellen Version)

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

Weitere Informationen

DELETE (Transact-SQL)
DROP TABLE (Transact-SQL)
IDENTITY (Eigenschaft) (Transact-SQL)