What are the difference between Delete and Truncate in Sql Server

Following are few differences.

1.

Truncate is a DDL Command.

Delete is a DML Command.

2.

Truncate operation cannot be rolled back.

Delete operation can be rolled back.

3.

By using Truncate operation, it deletes everything and reconstructs the table structure (schema). We can’t retain the values.

By using Delete we can retain the deleted values.

4.

We cannot use where clause in truncate.

Where clause can be used with Delete.

5.

By using Truncate operation, all the identities will be deleted.

By using Delete identities cannot be deleted.

6.

The syntax for truncating a table is:

            truncate table tablename

The syntax for deleting rows from a table is:

      delete from table or

 delete from table where condition

7. 

Truncate table is faster and uses fewer system and transaction log resources than Delete.

The Delete statement removes rows one at a time and records an entry in the transaction log for each deleted row.

8.

Because Truncate table is not logged, it cannot activate a trigger.

It can activate a trigger.