Delete versus Truncate

As far as I know, there are two ways to delete data from a table. These are the Delete and Truncate function. You can also drop the entire table, but this post focuses around clearing the data only.

Reading into it, comparing Delete and Truncate does seem to be like comparing apples and oranges. As you’ll soon see.

Note – database engines may handle this differently, but I will be looking at how SQL Server does it.

Statement type

Delete is DML, Truncate is DDL.

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, INSERT statements. DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Space reclamation

Delete does not recover space, Truncate recovers space.

Row scope

Delete can be combined with a where clause, to only remove some data that meets the given criteria. Truncate removes all rows.

Identity Columns

Truncate resets the sequence for IDENTITY column types, delete does not.

I hope that helps!

Subscribe

0 comments