Difference between Truncate Table and Delete Table (Truncate Vs Delete)

Today, In this blog post, I would like to point out few differences between Truncate and Delete SQL Statement. This is also one of the most asked questions in SQL Server Interviews (specifically for SQL developers)

TRUNCATE Table

• Fastest method for deleting all the rows from a table.
• We can say Truncate Table is similar to Delete Statement without WHERE clause (for.e.g. DELETE FROM EMP)
• Uses fewer system and transaction log resources. Only the page deallocations are recorded in the Transacton Log.
• Fewer locks are used. Truncate table always locks the table and page but not the row.
• Without exception, zero pages are left in the table
• If the column in a table is having IDENTITY colum then Truncate Table command will reset the identity to it’s seed value. If no seed is defined then value 1 is used as default value.

DELETE Table

• Deletes one row at a time and each deleted row is logged in the transaction log.
• Delete statement locks each row for deletion (when used with Row Lock)
• Once the delete process is over, table still contains the empty pages in a heap.
• If the DELETE statement doesn’t use the Table Lock then the table may contain many empty pages .
• To deallocate the empty pages, use TABLOCK hint in the DELETE statement. This will put a shared lock on the table instead of row or page lock and deallocate the pages.

Note: When empty pages are not de allocated, the space cannot be reused by other objects.

Example

TRUNCATE TABLE EMP_TEST;

DELETE FROM EMP_TEST WITH (TABLOCK);

 

Happy Learning !!!