Delete vs Truncate

Most Frequently Asked Question from /  by  SQL Developers

	Delete	Truncate
1	DML (Data Manipulation Language)	DDL( Data Definition Language )
2	AS delete is DML so  Data Definition Language  log are created	No transaction log are created
3	We can delete all rows from table or based on condition ie. Specifying condition on where clause.	Will delete all rows .
4	As delete creates logs it is slow but can rolled back	It is very fast roll back is not possible.
5	Data from reference tables  can be deleted .(On cascaded )	Truncate table does not work on referenced tables .
6	Identity columns are not reseeded  .	All Identity column are reseeded

7

Delete can leave empty pages in table since as page removal requires a table lock which doesn't necessarily happen.

Truncate removes all pages. No empty pages are left behind in a table.

8

Delete command acquires table and/or page and row locks for the whole table.

Truncate acquires only table and page locks for the table. Since no row lock are used less memory is required.

9

On Delete all delete triggers will be fired.

No trigger will be fired upon Truncate command.

·        




E      Execution of each command has a log ( Not  transaction log)