Roadmap › SQL Server & T-SQL › Module: Database Design
Lessons in this course (19)
3. DDL vs DML; DELETE vs TRUNCATE vs DROP
BasicMediumLesson 3 of 19
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Type | DML | DDL | DDL |
| Removes | Rows (with optional WHERE) | All rows | The whole table (structure + data) |
| Logging | Each row logged | Page deallocations logged (minimal) | Metadata |
| Triggers | Fires DELETE triggers | No | No |
| Identity | Not reset | Reset to seed | Gone |
| Rollback | Yes (in a transaction) | Yes, inside a transaction in SQL Server | Yes, inside a transaction |
| FK-referenced table | Allowed | Not allowed | Not allowed |
| Speed | Slower | Fast | Fast |