Delete Vs Truncate

Delete Vs Truncate

	Delete	Truncate
Command	DELETE FROM <<TableName>>	TRUNCATE TABLE <<TableName>>
Speed	Slower	Faster
WHERE	Can be applied	Cannot be applied
IDENTITY	Cannot reset	Will reset
TRIGGER	Will fire	Won’t fire
Transaction Log	Maintains record level log	Maintains page level log
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
Table Variable	Can be deleted	Cannot be truncated
CDC	Will allow	Won’t allow if CDC is enabled on table
Rollback	Can be rolled back	Can be rolled back (yes, this is true, Truncate can be rolled back)