Difference between Truncate and Delete in SQL

Truncate and Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure. Both statements delete the data from the table not the structure of the table.

	Delete	Truncate
Speed	Slower	Faster
WHERE	Can be applied	Cannot be applied
IDENTITY	Cannot reset	Will reset
TRIGGER	Will fire	Won’t fire
Transaction Log	Will be logged	Won’t be logged
REFERENTIAL INTEGRITY	If Child table doesn’t have corresponding record from master table then it will allow	Will never ever allow if any referential integrity exists; no matter child table has record or not
DATA COMMAND	DML (data manipulation language) command.	DDL (data definition language)
ROLLBACK	Can’t rollback	Will rollback
TABLE VARIABLE	Can be deleted	Cannot be truncated
CDC(Change Data Capture)	Will allow	Won’t allow if CDC is enabled on table

EXAMPLE

CREATE TABLE Student

(

      sid INT identity PRIMARY KEY,

      Name Varchar(255) NOT NULL

)

INSERT INTO Student(Name)VALUES('abc')

INSERT INTO Student(Name)VALUES('pqr')

INSERT INTO Student(Name)VALUES('xyz')

-- Syntax of Delete command

DELETE Student WHERE sid=2

-- Syntax of Truncate Command

TRUNCATE TABLE Student