SQL-Montag: Was ist der Unterschied zwischen “Delete From” und “Truncate”?

Monday, March 28, 2011 10:42:28 PM (W. Europe Daylight Time, UTC+02:00)

Thomas Mentzel hat in seinem Blog eine ziemlich coole Aktion gestartet, bei der er am Anfang der Woche fünf Fragen stellt.
Jede Frage bezieht sich auf einen anderen Werktag der Woche und behandelt ein Thema aus dem .NET-Programmierumfeld.

Die erste Frage, die er zum heutigen “SQL-Montag” gestellt hat, ist: Was ist der Unterschied zwischen Delete From und Truncate?

Diese Frage möchte ich gern beantworten.

delete-truncateDelete und Truncate sind T-SQL Befehle, mit denen Datensätze aus Tabellen einer MS-SQL Datenbank gelöscht werden können.
Das sind auch schon die Gemeinsamkeiten. Die Unterschiede liegen im Detail.

Der Befehl Delete löscht die Datensätze physikalisch und speichert das Löschen jeder einzelnen Zeile im Transaction-Log ab. Werden viele Zeilen gelöscht, wächst das Log File entsprechend schnell.


Werden Datensätze mit Truncate gelöscht, werden die Datensätze nicht physikalisch gelöscht, sondern werden nur zum überschreiben freigegeben. Das Freigeben der Daten wird ebenfalls geloggt, nur auf eine andere Art und Weise.
Dadurch ist Truncate zwar schneller, hat aber auch einige Nachteile:

  • Um ein Truncate Statement ausführen zu dürfen, muss man db_owner, ddl_admin oder Besitzer der Tabelle sein.
  • Truncate funktioniert nicht bei Tabellen, die FOREIGN KEY Constraints haben.
  • Truncate kann keinen Trigger aktivieren.
  • Die Daten einer Tabelle können nicht mit Truncate gelöscht werden, wenn die Tabelle Grundlage einer View ist.

Ein weiterer wesentlicher Unterschied ist, dass man bei Delete die zu löschenden Daten mit Hilfe der Where Bedingung einschränken kann. Mit Truncate können nur ganze Tabellen gelöscht werden.

Löscht man Daten aus einer Tabelle, die eine IDENTITY-Spalte hat, kann man beim Truncate Statement feststellen, dass der Wert der IDENTITY-Spalte auf den Ausgangswert (in der Regel 0) zurück gesetzt wird.
Löscht man die Daten hingegen mit dem Delete Statement, sieht man, dass der Zähler der IDENTITY-Spalte erhalten bleibt.

Hierzu noch ein Tipp: Möchte man mit Delete löschen, weil man beispielsweise eine Where Bedingung braucht, kann man den Zähler der IDENTITY-Spalte nachträglich auf den Ausgangswert mit Hilfe des folgenden Befehls auf 0 zurück setzen:

DBCC CHECKIDENT (Tabellenname, reseed, 0)

Wer beantwortet morgen die Frage zum Thema LINQ? Smiley

Wenn ihnen der Artikel gefallen hat oder er für sie hilfreich war,
bitte "kicken" sie ihn.

Kick it on dotnet-kicks.de