DELETE logged die Daten für jede einzelne Zeile, die von dem Statement betroffen sind und entfernt physikalisch den Record aus der Seite. Fährt man seine Datenbanken im "Full Recovery" Modus, kann die Aufzeichnung eines jeden einzelnen betroffenen Datensatzes das Transaktionsprotokoll enorm aufblähen. Dies ist aber notwendig, damit SQL Server im Falle einen Falles die Datenbank so aktuell wie möglich wiederherstellen kann. Der Umstand, das jeder Datensatz protokolliert wird, macht auch verständlich, daß umfangreiche DELETE Operationen langsam sind.

Im allgemeinen ist TRUNCATE TABLE schneller als DELETE aufgrund der Art, wie Daten *entfernt* werden. Bei Verwendung von TRUNCATE werden tatsächlich keine Daten entfernt, vielmehr werden die Datenseiten als frei markiert (deallokiert) und die Pointer auf Indexes und die erste Seite entfernt. Die Daten existieren physikalisch auch weiterhin bis sie überschrieben werden oder die Datenbank verkleinert wird. Diese Aktion bedeutet nur einen sehr geringen Aufwand und ist daher sehr schnell. Es ist ein weitverbreiteter Irrtum, zu glauben, TRUNCATE wird nicht gelogged. Dies ist falsch! Die Deallokation der Datenseiten wird sehr wohl im Transaktionsprotokoll aufgezeichnet. Daher erscheint TRUNCATE TABLE auch als 'minimally logged' in BOL. TRUNCATE kann innerhalb einer Transaktion verwendet werden. Erfolgt dann ein ROLLBACK der Transaktion werden bereits deallokierte Seiten wieder als allokiert gekennzeichnet und damit der ursprüngliche Zustand vor Ausführung von TRUNCATE wiederhergestellt.

Einige Beschränkungen für TRUNCATE existieren:
1) Man muss db_owner, db_ddladmin oder Besitzer der Tabelle sein
2) TRUNCATE TABLE funktioniert nicht bei Tabellen, die durch eine FOREIGN KEY CONSTRAINT referenziert werden.

Ferner setzt TRUNCATE TABLE einen evtl. vorhandenen IDENTITY Wert zurück, DELETE nicht.

Wenn nun also TRUNCATE so wahnsinnig viel schneller als DELETE ist, warum dann überhaupt DELETE verwenden?

Nun, TRUNCATE ist ein Alles-oder-Nichts Ansatz. Man kann nicht mit chirurgischer Präzision angeben, nur die Datensätze zu entfernen, die einem bestimmten Kriterium entsprechen. Entweder alle Zeilen oder keine. Allerdings kann man auch einen Workaround einsetzen. Mal angenommen, es sollen mehr Zeilen aus einer Tabelle gelöscht werden als nachher verbleiben. In diesem Fall könnte man diejenigen Daten, die nicht gelöscht werden sollen, in eine temporäre Tabelle exportieren, das TRUNCATE ausführen und anschließend die Daten aus der temporäre Tabelle zurückimportieren. Besitzt man eine Spalte mit der IDENTITY Eigenschaft, und man möchte den originalen Wert dieser Spalte erhalten, muß man vor dem Reimport aus der temporäre Tabelle IDENTITY_INSERT < Tabelle > auf ON setzen. Danach können die Zeilen, die erhalten bleiben sollen, reimportiert werden. Anschließend IDENTITY_INSERT auf OFF setzen. Meiner Erfahrung nach bestehen gute Chancen, trotz dieses Workarounds schneller zum Ziel zu kommen als bei der Verwendung von DELETE. Eventuell kann man man noch den Recovery Modus der Datenbank auf "Einfach" setzen, bevor man den Export in die temporäre Tabelle beginnt, und am Ende wieder auf "Vollständig", um die Operation zu beschleunigen. Allerdings riskiert man durch Wechsel des Recovery Modus, die Datenbank nur bis zum letzten Backup wiederherstellen zu können. Ob einem dies genügt, muß jeder selbst entscheiden.

Anmerkung 29.07.2004: Will man nicht auf TRUNCATE verzichten, kann man die FK's skripten, droppen, das TRUNCATE ausführen und danach anhand des Skripts die FK's neu erstellen. In DMO ist das einfacher als in T-SQL.