TRUNCATE TABLE (Transact-SQL)
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 }
[ ; ]
Hinweis
Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) und 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 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 WortTO
getrennt 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 mitTRUNCATE 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 derEXPLAIN
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.
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
Überprüfen Sie die Daten vor dem Abschneiden.
SELECT * FROM TruncateTest; GO
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.
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.
Bereinigen Sie die Tabelle.
DROP TABLE TruncateTest; GO