SQL Server – Difference between TRUNCATE and DELETE statement

Difference between TRUNCATE and DELETE statement

TRUNCATE and DELETE commands are used to delete data from table. Why there are two commands for same task? Do they work similarly? Is there any difference between them? Yes, their major differences between them how they work.

The main difference is that TRUNCATE deletes all the rows from the table and reset the IDENTITY for the column to seed value. Where as DELETE can be used to delete all the rows as well as the selected rows by using it with WHERE clause. Also it does not rest the IDENTITY for the column.

More detailed text book difference between TRUNCATE and DELETE statements is given below:

TRUNCATE Command

  • TRUNCATE is faster and uses fewer system and transaction log resources then DELETE.
  • TRUNCATE removes the data by de-allocating the data pages used to store the table’s data, and only the page de-allocations are recorded in the transaction log.
  • TRUNCATE removes all rows from a table, but the table structure and its column, constraints, indexes, and so on remains.
  • You cannot use TRUANCATE TABLE on a table referenced by a FOREIGN KEY constraint.
  • Because TRUNCATE TABLE is not logged, it cannot activate the trigger.
  • TRUNCATE cannot be rolled back.
  • TRUNCATE is DDL (data definition Language) command.
  • TRUNCATE resets IDENTITY for the column to seed value.

Example:

TRUNCATE TABLE Department

DELETE Command

  • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
  • DELETE does not reset IDENTITY counter for the column.
  • DELETE can be used with or without WHERE clause.
  • DELETE activates trigger.
  • DELETE can be rolled back
  • DELETE is DML command.

Example:

— Deletes selected rows

DELETE FROM Department WHERE DeptID=102

— Delete all the rows

DELETE FROM Department

Both TRUNCATE and DELETE commands do the same task but differently. They are meant to be used as per the requirement.

Good Luck!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: