TRUNCATE Table Vs DELETE Table

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)