RSS

TRUNCATE vs. DELETE in SQL Server

06 Aug

TRUNCATE

  • TRUNCATE is faster and uses fewer system and transaction log resources than DELETE. (Read all the points below)
  • 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 the rows from a table, but the table structure, its columns, constraints, indexes and so on remains. 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.
  • Using T-SQL – TRUNCATE cannot be rolled back unless it is used in TRANSACTION. OR TRUNCATE can be rolled back when used with BEGIN … END TRANSACTION using T-SQL.
  • TRUNCATE is a DDL Command.
  • TRUNCATE resets the identity of the table.

DELETE

  • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
  • DELETE does not reset Identity property of the table.
  • DELETE can be used with or without a WHERE clause.
  • DELETE activates Triggers if defined on table.
  • DELETE can be rolled back.
  • DELETE is DML Command.
  • DELETE does not reset the identity of the table.
 
Leave a comment

Posted by on August 6, 2013 in MS SQL Server

 

Tags: , , , , , ,

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: