Diese Aussage habe ich heute während eines Kundentermins von einem DBA gehört. Solche “Mythen” werden immer wieder mal über die SQL Foren verbreitet und dann – leider – durch Mundpropaganda gerne in die Unternehmen getragen. Diese Aussage ist FALSCH! Selbstverständlich wird ein TRUNCATE TABLE protokolliert– und zwar genau so, wie ein DELETE dbo.relation. Dieser Artikel beschreibt und belegt detailliert, dass man Aussagen, wie den obigen möglichst skeptisch gegenüber stehen sollte und nicht immer alles glauben darf, was man so hört.

Bevor es in die Details geht, zunächst ein kleines Experiment; dieses Experiment belegt bereits, dass es sich bei TRUNCATE TABLE um eine protokollierte Transaktion handelt.

-- Erstellen einer einfachen Relation
CREATE TABLE dbo.mytable
(
    id    int      NOT NULL IDENTITY(1, 1),
    col1  char(20) NOT NULL,
    col2  char(20) NOT NULL,
    CONSTRAINT pk_mytable_id PRIMARY KEY CLUSTERED (id)
);

-- Eintragen von 100.000 Datensätzen
DECLARE @i int = 100000
WHILE @i <= 100000 BEGIN
    INSERT INTO dbo.mytable (col1, col2)
    SELECT 'Value: ' + CAST(@i AS varchar(2)),
           'Value: ' + CAST(@i % 10 AS varchar(2))

    SET    @i += 1
END

-- Ergebnis
SELECT * FROM dbo.mytable;

Nachdem die Relation mit ein paar Datensätzen angelegt wurde, kann das folgende Statement ausgeführt werden:

-- Löschen aller Daten aus der Relation mit TRUNCATE
BEGIN TRANSACTION
    TRUNCATE TABLE dbo.myTable;
    SELECT * FROM dbo.myTable;
ROLLBACK TRANSACTION

SELECT * FROM dbo.myTable;

Simpel: Innerhalb einer Transaktion wird der Inhalt der Relation mit TRUNCATE gelöscht und anschließend der Inhalt angezeigt. Im Anschluss wird die Transaktion abgebrochen und erneut der Inhalt überprüft.

Vor Transaktion	In Transaktion	Nach Transaktion

Wie man deutlich erkennen kann, wird durch ein ROLLBACK die Transaktion rückgängig gemacht. Warum also hält sich dieser Mythos so beständig. Vermutlich rührt diese Aussage auf der Beobachtung, dass TRUNCATE um ein Vielfaches schneller ausgeführt wird, als ein DELETE. Der Unterschied in der Ausführungsgeschwindigkeit beider Verfahren ist die Art und Weise, wie der “Löschvorgang” intern durchgeführt wird. Für das Verständnis reicht ein – erster – oberflächlicher Blick auf die physikalischen Indexstrukturen. Die nachfolgenden Skripte machen den Unterschied deutlich:

-- Löschen aller Daten aus der Relation mit TRUNCATE
BEGIN TRANSACTION
    TRUNCATE TABLE dbo.myTable;
    SELECT  i.name,
            ps.index_level,
            ps.page_count,
            ps.record_count,
            ps.ghost_record_count
    FROM    sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.mytable', 'U'), 1, DEFAULT, 'DETAILED') ps
            ON (
                 i.object_id = ps.object_id AND
                 i.index_id = ps.index_id
               )
ROLLBACK TRANSACTION

Nach einem TRUNCATE sind KEINE Informationen über allokierten Speicher mehr vorhanden!

-- Löschen aller Daten aus der Relation mit DELETE
BEGIN TRANSACTION
    DELETE dbo.myTable;
    SELECT * FROM dbo.myTable;
    SELECT  i.name,
            ps.index_level,
            ps.page_count,
            ps.record_count,
            ps.ghost_record_count
    FROM    sys.indexes i INNER JOIN sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('dbo.mytable', 'U'), 1,DEFAULT, 'DETAILED') ps
            ON (
                 i.object_id = ps.object_id AND
                 i.index_id = ps.index_id
               )
ROLLBACK TRANSACTION

Bei einer DELETE-Aktion bleiben die Schemadaten vollständig erhalten. Somit MUSS es einen Unterschied geben. Schaut man in die Online Hilfe von Microsoft SQL Server, mag es auf Grund der dortigen Aussage leicht zu Missverständnissen kommen:

“Die TRUNCATE TABLE-Anweisung ist ein schnelles, effizientes Verfahren zum Löschen aller Zeilen einer Tabelle. TRUNCATE TABLE ist der DELETE-Anweisung ohne eine WHERE-Klausel ähnlich. TRUNCATE TABLE ist jedoch schneller und verwendet weniger Systemressourcen und Ressourcen für die Transaktionsprotokollierung.”

Die Online Hilfe schreibt, dass beide Verfahren ähnlich sind; das ist aber NICHT der Fall. Zwischen beiden Verfahren liegt ein erheblicher Unterschied. Während DELETE eine DML-Aktion ist, ist TRUNCATE eine DDL-Aktion. Von DML-Aktionen spricht man, wenn Manipulationen auf Datenebene durchgeführt werden (SELECT, INSERT, UPDATE, DELETE). Von DDL-Aktionen spricht man, wenn es sich um Schema-Manipulation / Manipulation der Metadaten handelt. Und hier genau liegt der Unterschied, den man wunderbar durch einen Blick in das Transaktionsprotokoll und die Transaktionssperren belegen kann.

TRUNCATE intern

Zunächst einmal ein Blick auf das Transaktionsprotokoll und die Transaktionssperren bei einer TRUNCATE-Aktion. (Da es sich um ein Testsystem handelt, ist nur eine Transaktion aktiv – von daher muss nicht auf eine LSN gefiltert werden!)

SELECT [Current LSN],
       Description,
       Operation,
       [Lock Information],
       [Rows Deleted],
       [Log Record]
FROM   fn_dblog(NULL, NULL)
ORDER BY
       [Current LSN];

DBCC TRACEON (3604);
DBCC PAGE ('db_demo', 1, 106, 1)

DELETE intern

Statt eines TRUNCATE wird nun ein DELETE ohne WHERE-Klausel ausgeführt und erneut das Transaktionsprotokoll untersucht

SELECT resource_type,
       OBJECT_NAME(resource_associated_entity_id) AS object_name,
       request_mode,
       request_type,
       request_status
FROM   sys.dm_tran_locks
WHERE  request_session_id  = XX; -- Eigene SPID eintragen, in der die Transaktion läuft!

Während des Löschvorgangs wird eine exklusive Sperre auf die komplette Relation gesetzt – macht Sinn, da ja die Relation vollständig geleert werden soll.

Berechtigungen TRUNCATE vs. DELETE

Ein dritter – wichtiger – Hinweis auf deutliche Unterschiede zwischen TRUNCATE und DELETE beschreibt die erforderlichen Berechtigungen für die jeweiligen Transaktionen. Wie oben gezeigt, handelt es sich bei TRUNCATE um eine DDL-Aktion während DELETE eine DML-Aktion ist. Aus diesem Grund ist ableitbar, warum TRUNCATE die Berechtigung ALTER benötigt während für DELETE eine DELETE-Berechtigung für das betroffene Objekt ausreicht. Die Berechtigung ALTER hat weitreichende Konsequenzen für das Sicherheitskonzept, da mit dieser Gewährung ALLE Manipulationsmöglichkeiten auf Schemaebene für den Berechtigten einher gehen.

Fazit

Der Artikel zeigt, dass es Unterschiede zwischen TRUNCATE und DELETE gibt; Er belegt, dass die Aussage, “TRUNCATE wird nicht protokolliert” nicht wahr ist. TRUNCATE verfolgt zwar einen anderen Ansatz, ist aber eine VOLLSTÄNDIG protokollierte Transaktion. Auf Grund der unterschiedlichen Arbeitsweise ist das Transaktionsprotokoll deutlich geringer als beim Löschen mittels DELETE – und daher kommen auch die Zeitunterschiede. TRUNCATE bedeute eine Neuerstellung der Relation auf Grund der Löschung von allokierten Datenseiten aus den Verwaltungsseiten (PFS / IAM).

Das kann man sehr gut beobachten, wenn man mal folgende Aktion durchführt:

1. Lassen Sie sich die Datenstrukturen der Relation ausgeben, wie im Artikel “Neue DMF für Struktur der Datenseiten (Pages)” beschrieben
2. Löschen Sie alle Daten aus der Relation mittels TRUNCATE
3. Lassen Sie die Abfrage wie in Schritt 1 beschrieben, erneut ausführen – es werden KEINE Datenseiten mehr vorhanden sein!
4. Fügen Sie neue Datensätze in die zuvor mit TRUNCATE geleerte Relation ein
5. Lassen Sie die Abfrage wie in Schritt 1 beschrieben, erneut ausführen – nun werden vollständig neue Datenseiten allokiert!

Merke: JEDE Datenmanipulation (Schemata oder Benutzerdaten) wird vollständig protokolliert. Microsoft SQL Server folgt für ALLE Aktionen IMMER dem ACID-Prinzip!

• Atomicity
• Consistency
• Isolation
• Durability

Herzlichen Dank fürs Lesen!