DELETE AND TRUNCATE DIFFERENCES.

TRUNCATE

1. It will remove all the rows from the table and there will be no data in the table.

2. We cannot have put where clause with truncate.

3.Truncate removed all rows but the table structure , columns , constraints , indexes and so on.

4.Truncate cannot be rolled back.

5.It is a DDL Command.

6.Truncate resets the identity of the table.

7.Truncate is faster and uses fewer system and transaction log resources than delete.

8.You cannot truncate table which is referenced by a foreign key constraint.

9.Truncate can be rolled back if you are using transactions.

10.You cannot use TRUNCATE TABLE on tables that:
a)Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
b)Participate in an indexed view.
c)Are published by using transactional replication or merge replication.
For tables with one or more of these characteristics, use the DELETE statement instead.

TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.

DELETE

1.Delete command removes the rows from a table based on the condition that we provide with a where clause.

2.Delete removes rows one at a time and record an entry in the transaction log ( .ldf file ) for each deleted row.

3.Delete can be rolled back.

4. It is DML command.

5.Delete does not resets the identity of the table.

6.Delete uses more resources than truncate.

7.Delete can be used with where clause or without where clause.

Pawan Kumar

Pawankkmr@hotmail.com

Advertisements