Difference between DELETE & TRUNCATE commands in SQL?

TRUNCATE SQL Command:

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE can not be Rolled back.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.

DELETE SQL Command:

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

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE can be Rolled back.

DELETE is DML Command.

DELETE does not reset identity of the table.

In simple words, after deleting the records you can able to rollback but in case of Truncate once you delete the records you will not be able to rollack what you had deleted earlier.

TRUNCATE SQL Command:

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.

Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE can not be Rolled back.

TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.

DELETE SQL Command:

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

If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

DELETE Can be used with or without a WHERE clause

DELETE Activates Triggers.

DELETE can be Rolled back.

DELETE is DML Command.

DELETE does not reset identity of the table.

Good post. truncate is best suited for purging database and delete is great for transactional remove operation. see here for some more difference between truncate and delete in SQL

when we don't specify condition statement on delete operation it is same with that of truncate operation.
According to my point of view the major difference between truncate and delete is:
1.Truncate operation remove the tuple(row) permanently form the relation(table) with no rollback option where as delete operation remove the row temporarily with rollback option..

with my best of knowledge in sql delete is dml command means structure will exists
along with that we can change the structure

where as with truncate we can perform only removing rows with out changing its structure

truncate performs only on simple tables i.e not in referencing tables

To view difference between these two visit
Difference and to view more difference kindly visit More

This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check out this link...

http://mindstick.com/Articles/9c4a80f3-64be-41b2-a0b8-ba7e26590e06/?Basic%20SQL%20Query

Thanks