difference between DELETE & TRUNCATE commands

Where Condition

• Delete Removes the rows from a table based on the condition that we provide with a WHERE clause.

• Truncate Actually remove all the rows from a table(i.e there is no where condition on this)

Transaction log

Truncate:

• 
  
  Is faster and uses fewer system and transaction log resources than DELETE.
  
  
  
• 
  
  Deallocates the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
  
  
  
• 
  
  directly delete records without entry in transaction log.
  
  
  
• 
  
  It Can't be rollbacked
  

TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed.
But can be rollbacked the following way

____________________________________________________________

 CREATE TABLE dbo.TEST_TRUNCATE (COL1 INT IDENTITY, COL2 NVARCHAR(100));

SET NOCOUNT ON
GO
INSERT INTO dbo.TEST_TRUNCATE (COL2) VALUES ('TEST TRUNCATE');
GO
 BEGIN TRAN
/*Show me the count of the records before truncate*/

SELECT COUNT(*) FROM dbo.TEST_TRUNCATE

TRUNCATE TABLE dbo.TEST_TRUNCATE

/*Show me the count of the records now*/

PRINT 'INSIDE THE TRANSACTION'

SELECT COUNT(*) FROM dbo.TEST_TRUNCATE

/*Rollback now*/

ROLLBACK TRAN

/*Show me the count of the records now*/


PRINT 'OUTSIDE THE TRANSACTION'

SELECT COUNT(*) FROM dbo.TEST_TRUNCATE
______________________________________________________________________________
 
Delete:

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

Identity

Truncate

• 
  
   truncate table the counter used by an identity column for new rows is reset to the seed for the column
  
  
  

Delete

• 
  
   If you want to retain the identity counter, use delete statement instead.
  
  
  

Table referenced by a foreign key constraint

Truncate

• 
  
  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.
  
  
  

 Delete

• 
  
  Delete statement without a where clause works.
  
  
  

Truncate table may not be used on tables participating in an indexed view.

TRUNCATE is DDL Command

DELETE is DML Command

Performance

TRUNCATE TABLE is faster and uses fewer system and transaction log resources. 

Delete Table is slower

The DELETE statement is executed using a row lock, each row in the table is locked for deletion.

Locking

TRUNCATE TABLE always locks the table and page

The DELETE statement is executed using a row lock, each row in the table is locked for deletion