The transact SQL command "truncate table" is a minimally-logged operation. As such, a truncate table is equivalent to but faster than a delete command without a where clause. Delete removes rows one at a time and logs each deleted row as a transaction; truncate table de-allocates whole data pages and makes fewer log entries. Both delete and truncate table reclaim the space occupied by the data and its associated indexes. Because the deleted rows are not logged individually, truncate table cannot fire a trigger. Since it is a non-recoverable command, then only the owner of the table can truncate the table. Normal users cannot truncate the table.
If you have an application which requires a table to be truncated after certain operations by the user than you, you may want to consider using a temporary table in tempdb (the so-called #table) for this purpose. If the table is a staging table in the database and has to be there all the time, then delete must be used. The problem with deleting all records is that it can cause the transaction log of database to fill up. For this reason, you may consider looping through records in a batch size, of say, 10,000 records, at a time and commit, afterwards, as the following example shows:
set rowcount 10000
while exists (select 1 from table_a)
delete from table_a
if @@error != 0 or @@transtate = 3
print "ERROR : delete of table_a failed "
if @@trancount > 0 rollback tran
This was first published in December 2005