Priyank s Pathania

• Sep 26th, 2005

 

Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and can be rolled back.

Sudeep Biswas

• Nov 8th, 2006

 

TRUNCATE does not records the transactions that delete the rows from a table thus it will not be recoverable if thr transaction is rolled back.DELETE on the other hand recods the transactions and hence rows deleted can be recovered if rolled back before committing the transaction.

saravanan p

• Dec 4th, 2006

 

Hi,

1. For truncate command, trigger will not get fired, but for Delete Command, Trigger will get fired.

2. Truncate Is the DDL command whereas Delete is the DML command

3.Once the truncate command is issued, Transactions can't be rolled back whereas transactions can be rolled back for Delete command.

Thanks and Regards

Saravanan P

psingla

 Profile Answers by psingla Questions by psingla

• Mar 16th, 2010

 

SQL Sever:
Truncate:
Delete all the records from the table.
Do not keep the deleted record in the log file.
Reset the identity column to 1.
Use fewer locks with table level locking.
Tollback is not possible
Delete trigger does not gets fired
All the pages used by the table gets released
Speed is fast

Delete:
Deletes some or all records from the table depending upon the filter criteria given in where clause
Keep the deleted record in the log file
Do not reset the identity column.
Use complex locking system using row level and page level locking
Rollback is possible
Delete trigger gets fired
Empty pages may still exists in table
Speed is slow