Difference between truncate table and delete table commands with examples

Difference between truncate and delete commands with examples

Truncate

Truncate is a DDL command. This command is used to delete all the records from the table permanently.

Syntax: TRUNCATE TABLE <TABLE_NAME>;

Delete

Delete is a DML command. This command is used to delete all the records and also delete specific records by using where clause from the table.

Syntax: DELETE FROM <TABLE_NAME> [WHERE <CONDITION>];

Truncate	Delete
DDL command	DML command
Faster	Slower
Reset identity column (Schema Lock)	Leaves identity column
Permanent deletion. Rollback the data is not possible.	It is a temporary deletion (Full-logged recovery model). Rollback the data is possible
Specific record deletion is not possible.	Delete a specific record by using where clause.
Doesn't invoke delete triggers.	Invoke delete triggers.
Removes all pages	Can leave the empty pages, which can be removed by using the table rebuild.
Table lock ( inc schema lock)	Exclusive lock
Removes all rows. Deallocates the data pages in a table and only this deallocation is stored in the transaction log.	Removes one row at a time and the deleted row is stored in the transaction log.

Let us create a table and insert some sample data.

CREATE TABLE TRUN_TBL(ID INT IDENTITY NOT NULL PRIMARY KEY, NAME VARCHAR(8000) NOT NULL DEFAULT REPLICATE('A',5000))
CREATE TABLE DEL_TBL(ID INT IDENTITY NOT NULL PRIMARY KEY, NAME VARCHAR(8000) NOT NULL DEFAULT REPLICATE('A',5000))
GO
INSERT INTO TRUN_TBL DEFAULT VALUES
GO 20
INSERT INTO DEL_TBL DEFAULT VALUES
GO 20
-- TRUN_TBL statistics
exec sp_spaceused TRUN_TBL
dbcc showcontig(TRUN_TBL)
-- DEL_TABL statistics
exec sp_spaceused DEL_TBL
dbcc showcontig(DEL_TBL)

As you see, there are 20 pages (3 extents) and 200kb data size occupied in the tables. 

Next delete all the records in both the tables and check the statistics again.

truncate table TRUN_TBL
delete from DEL_TBL

Now the TRUN_TBL table has 0 pages that mean it deallocates all the data pages.

The DEL_TBL table has 1 page (72kb), as delete command can leave empty pages in a table since an empty page requires table lock which doesn't necessarily happen. We can remove this unused data by using the alter table rebuild.

ALTER TABLE DEL_TBL REBUILD

Thanks for your time.