Interview Question - What is the difference between TRUNCATE and DELETE ?

I am not sure why this question is fielded in the interviews but over the last couple of years I have seen/heard it enough times to be made as a blog entry..

The question is simple - What is the difference between TRUNCATE and DELETE ? 

Many of you must be aware of the most of the differences that are listed below but it might turn into worth reading if you find something new 

Please run following queries against any TEST database that you have to see how allocation looks like after DELETE (all rows) and TRUNCATE is executed 

CREATE TABLE tbl_testDelete 

(

ID INT,

NAME VARCHAR(8000)

)

DECLARE @I INT = 1

WHILE (@I < 100)

BEGIN

INSERT INTO tbl_testDelete

SELECT @I, REPLICATE('*',8000)

SET @I = @I + 1

END

DELETE FROM tbl_testDelete

EXEC SP_SPACEUSED 'tbl_testDelete'

TRUNCATE TABLE tbl_testDelete

EXEC SP_SPACEUSED 'tbl_testDelete'

DROP TABLE tbl_testDelete

In SQL 2012 we have new dynamic management function to know the allocation details for the object .. dm_db_database_page_allocations

It requires 5 different parameters to extract the information for the object 

DatabaseId –  Not Null 

TableId – Can be NULL 

IndexId – Can be NULL 

PartitionId – Can be NULL 

Mode – either "LIMITED" or "DETAILED"

For the above query it can be used in following way .. Please try using it after both DELETE and TRUNCATE statement to see the difference.

SELECT * FROM sys.dm_db_database_page_allocations(DB_ID('YourDBName'),OBJECT_ID('tbl_testDelete'),NULL,NULL,'LIMITED')

For more details on the what TRUNCATE does internally please go through following link

https://msdn.microsoft.com/en-us/library/ms177570.aspx

While as you would have already noticed I didn't categorize them in DDL and DML because it is ambiguous and database experts around the globe are still debiting over this 

http://sqlblog.com/blogs/dejan_sarka/archive/2014/03/28/truncate-table-ddl-or-dml-statement.aspx