Elvileg mindkét paranccsal egy tábla tartalmát tudjuk törölni, de mivel külön van delete és külön van truncate, gondolhatjuk, hogy vannak különbségek. Van is különbség, mégpedig olyanok, amik miatt nagyon meg kell gondolni, hogy melyiket használjuk egy-egy esetben.
Mechanizmus
A delete paranccsal egy táblából szépen soronként tudunk törölni:
delete from [tabla]
A truncate paranccsal azt mondjuk a táblának, hogy ő most üres. Deallokálja a táblához tartozó data page-eket és ezzel mondhatnánk pointer to zero a táblán:
truncate table [tabla]
Szűrés
Delete végén megadhatunk where feltételt, hogy ne minden rekordot, hanem csak a feltételnek megfelelő sorokat töröljük:
delete from [tabla] where [oszlop] = ertek
Truncate nem tud feltételt fogadni, mindent vagy semmit alapon működik. (Szóba jöhet partitioning, ezzel kicsit még játszhatunk, de ez nem igazi where feltétel.)
Logolás
Az adatbázis recovery modeljétől (mondjuk logolási szintjétől) függően a delete parancs rendesen használja a tranzakciós logot. Minden visszakövethető, a törölt sorok visszaállíthatóak log alapján.
A truncate nem logol és pont. Illetve annyit logol, hogy ekkor és ekkor lefutott. Ezek alapján a tábla tartalmát nem lehet visszaállítani.
Identity
A delete érintetlenül hagyja az identity értéket, vagyis ha éppen 123456789-nél járt, akkor a következő beszúrt sornál ez 123456790 lesz (már persze ha 1 az identity increment).
A truncate simán legyalulja a táblát, ezért az identity értéket is kinullázza (pontosabban a seed értékére állítja be), elölről kezdi a számozást.
Trigger
Delete-nél ellövődnek (szépen magyarul) a triggerek, truncate esetén nem, mert nincs sor szintű logolás.
Foreign key
Ha egy táblára foreign key segítségével mutat egy másik tábla, attól még természetesen tudunk belőle törölni delete-tel, csak figyelembe kell venni a foreign key constraintet. Viszont truncate-et nem lehet futtatni egy ilyen táblára, szintén a működéséből adódóan. Nem soronként töröl, nem tudja így ellenőrizni a linkeket, ezért inkább nem is engedélyezett.
Replication és log shipping
Szintén a logolásra visszavezethető, hogy nem lehet truncate-elni olyan táblákat, amiket replikálunk vagy log shippingben részt vesznek
Indexed view
Ennek nem tudom pontosan az okát, de nem lehet olyan táblát truncate-elni, ami indexed view-ben szerepel. Gyanítom ez is azzal függ össze, hogy nem tudja a view indexét frissíteni, mert egyszer csak eltűnik minden adat belőle, anélkül, hogy bármi változás jelzés lenne.
Sebesség
A fentiek tükrében egyértelmű, hogy a delete eltarthat egy darabig, hiszen lehet, hogy rekordot keres (ha megadtunk feltételt), logol, lockol… míg a truncate nagyjából azonnali, hiszen sokkal kevesebb dolgot kell elintéznie.
