TRUNCATE Table Vs DELETE Table
October 3, 2009
Leave a comment
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)

Recent Comments