Pytanie zawarte w tytule wydaje się być banalne, ale spotkałem się już z osobami, które mimo dużego doświadczenia potrafiły udzielić nie do końca poprawnej odpowiedzi 😉
Nauka przez zabawę
Najprościej będzie wyjaśnić różnicę pomiędzy nimi za pomocą przykładów. Skorzystamy w nich z bazy StackOverflow2013 i tabeli dbo.Posts. Zaczniemy od wrzucenia pierwszych 100 000 wierszy do tabeli tymczasowej, na której będziemy przeprowadzać nasze eksperymenty.
DROP TABLE IF EXISTS #tempPosts;
SELECT TOP (100000)
*
INTO #tempPosts
FROM
[dbo].[Posts]
ORDER BY
[Id];
Mamy już naszą tymczasową tabelę, więc najpierw wypróbujmy składnię z DELETE w celu usunięcia wszystkich danych z tabeli. Włączę również wyświetlanie statystyk czasu i odczytów oraz plan zapytania.
SET STATISTICS TIME, IO ON; DELETE FROM #tempPosts;
Statystyki:
Table '#tempPosts'. Scan count 1, logical reads 18772, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 1033, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. (100000 rows affected) SQL Server Execution Times: CPU time = 359 ms, elapsed time = 389 ms.
Plan zapytania:
Nic nadzwyczajnego, szybki skan tabeli i usunięcie wszystkich wierszy. Dokonaliśmy przy tym 18772 odczytów (odczytami lob się tutaj nie interesujemy).
DELETE za nami, czas wypróbować TRUNCATE.
SET STATISTICS TIME, IO ON; TRUNCATE TABLE #tempPosts;
Statystyki:
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 3 ms.
Plan zapytania:
NULL
Co to się stanęło się? Statystyk żadnych, planu zapytania niet, czy na pewno wszystko się usunęło? Tak.
Różnica numer uno
DELETE jest operacją w pełni logowaną i operującą na poszczególnych wierszach w tabeli, więc aby usunąć wszystkie wiersze musi wykonać skan tabeli, a następnie każdy z otrzymanych wierszy fizycznie usunąć z pliku danych. TRUNCATE operuje na samych metadanych tabeli, oznaczając pierwszą stronę danych jako pustą, dzięki czemu jest minimalnie logowany i fizycznie nic nie usuwa, tylko informuje SQL Server, że “hej, to miejsce jest już wolne, nic tam nie ma”.
Wiąże się to jednak z pewnymi ograniczeniami TRUNCATE jakich nie posiada DELETE.
Wyobraźmy sobie, że chcemy usunąć tylko wpisy, których Id jest mniejsze od 1000. Przygotujmy więc zapytania z DELETE i TRUNCATE.
DELETE FROM #tempPosts WHERE [Id] < 1000; TRUNCATE TABLE #tempPosts WHERE [Id] < 1000;
Zapytanie z funkcją DELETE wykonało się i rzeczywiście usunęło z tabeli wszystkie posty z Id mniejszym od 1000.
Natomiast zapytanie z TRUNCATE trochę się na nas obraziło.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword ‘WHERE’.
Różnica numer uno uno
Klauzula TRUNCATE nie pozwala na warunkowe usuwanie rekordów. Możemy usunąć tylko całą zawartość tabeli.
Pobawmy się trochę relacyjnością danych.
Ciekawostka – Wiązania FOREIGN KEY na tabelach tymczasowych nie są utrzymywane, dostajemy od SSMS taki ładny komunikat:
Skipping FOREIGN KEY constraint 'whisper' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.
Musimy więc na nowo przygotować swoje dane, tym razem umieszczając je w “normalnych tabelach”.
DROP TABLE IF EXISTS dbo.tempFK; DROP TABLE IF EXISTS dbo.tempPosts; SELECT TOP(100000) * INTO dbo.tempPosts FROM [dbo].[Posts] ORDER BY [Id]; ALTER TABLE dbo.tempPosts ADD CONSTRAINT [evenindeath] PRIMARY KEY ([Id]); CREATE TABLE dbo.tempFK ([Id] INT NOT NULL CONSTRAINT [whisper] FOREIGN KEY REFERENCES dbo.tempPosts([Id]));
Do naszej nowej tabeli nic nie wrzucamy, jest pusta. Spróbujmy następnie usunąć wszystkie wiersze z tabeli dbo.tempPosts przy pomocy DELETE.
DELETE FROM dbo.tempPosts;
Wszystko ładnie się wykonało, 100 000 wierszy usunięte, ale plan zapytania lekko się odmienił.
Doszedł nam skan naszej dodatkowej tabeli, powiązanej poprzez klucz obcy. SQL Server musiał sprawdzić każdy usuwany wiersz czy przypadkiem nie ma referencji do niego w naszej dodatkowej tabeli, gdyż wtedy usunięcie wiersza z kluczem głównym złamałoby zasadę spójności danych (klucz obcy zostałby osierocony i tak naprawdę prowadził donikąd). Możemy to łatwo zauważyć wrzucając do naszej dodatkowej tabeli wiersz z wartością 11 i następnie próbując usunąć dane. Dostaniemy wtedy taki błąd.
Msg 547, Level 16, State 0, Line 18
The DELETE statement conflicted with the REFERENCE constraint “whisper”. The conflict occurred in database “StackOverflow2013”, table “dbo.tempFK”, column ‘Id’.
No dobrze, ale wróćmy do pustej tabeli tempFK i klauzuli TRUNCATE.
TRUNCATE TABLE dbo.tempPosts;
Msg 4712, Level 16, State 1, Line 19
Cannot truncate table ‘dbo.tempPosts’ because it is being referenced by a FOREIGN KEY constraint.
Ojoj…
Różnica numer uno uno uno
TRUNCATE nie pozwala na usuwanie zawartości tabel, do których w schemacie bazy są odwołania poprzez klucz obcy. Nigdy. Jeżeli chcemy skorzystać z klauzuli TRUNCATE, wcześniej musimy usunąć wszystkie powiązania do tabeli.
I teraz truskawka na torcie. Jak poszczególne zapytania zachowają się w ramach transakcji, która została cofnięta?
BEGIN TRAN
DELETE FROM dbo.tempPosts;
ROLLBACK
SELECT * FROM dbo.tempPosts;
Jak widać, wszystkie wiersze dalej są w tabeli, usunięcie ich zostało poprawnie wycofane. Czas na TRUNCATE.
BEGIN TRAN
TRUNCATE TABLE dbo.tempPosts;
ROLLBACK
SELECT * FROM dbo.tempPosts;
I tutaj również wszystko pozostało na swoim miejscu.
NIE-różnica numer uno
Tak samo jak DELETE, TRUNCATE również podlega transakcyjności i można go normalnie wycofać!
Gdybym dostawał 5 zł od każdego, kto zapytany o różnice między DELETE a TRUNCATE mówił, że “TRUNCATE nie można ROLLBACKować” to już bym mógł spokojnie spędzić weekend w pubie i to takim z piwem za 20 zł. Nawet Microsoft na swojej stronie ma taką piękną ramkę, bo mit ciągle żyje wśród ludzi:
I jeszcze kilka różnic, o których warto pamiętać.
Różnica numer uno uno uno uno
Wykonanie DELETE na tabeli wymaga posiadania uprawnień na poziomie DELETE na tabeli. Wykonanie TRUNCATE na tabeli wymaga posiadania uprawnień na poziomie ALTER TABLE.
Różnica numer uno uno uno uno uno
TRUNCATE TABLE omija wywołanie wszystkich wyzwalaczy (triggerów) przypisanych do operacji DELETE na tabeli.
Podsumowanie
Różnic oczywiście jest więcej, jak niemożność uczestniczenia tabeli “trankejtowanej” w widoku indeksowanym czy też to, że taka tabela nie może być “temporal” (nie mylić z temporary!). Dla chętnych, tutaj informacje od źródła o ograniczeniach i różnicach:
https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15#remarks
Oki, to pa!