Difference Between Truncate and Delete

Truncate and Delete both are used for deletion of data.But there are so many difference in between both.

1)TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server. While DELETE is a DML command and can be rolled back. Memory not released to server. After delete memory used by table will be same as before. But in Sql Server 2005 you can roll back Truncate also by using Transaction and even Delete command can not be roll back without Transaction.

2)Where clause can not be used in truncate while in delete you can.

3)A DDL it actually sets the high water mark level of the table back to zero (depends on MINEXTENTS too) hence no one can read the table records.The commend get itself issues a commit as it is DDL command hence can not be rolled back

4) In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired. But in Sql Server 2005 you can write Trigger for DDL also.

5) Do not Check Constraints.While Delete check.

When there is foreign key constraint even though there
is no data in child table Truncate cannot be done. only
when the constraint is dropped truncate can be executed.

Whereas though we have foreign key constraints if the child
table is deleted, then parent table can be deleted.

6) TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.

7) Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists. That’s Truncate is faster than delete.

Drop is similar to truncate only one difference is in case of drop table structure deleted too.


Ex.Below Example is true only for Oracle in SQL Server delete reduces the number of rows also.

Delete
SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
----------
14

SQL> DELETE FROM emp WHERE job = 'CLERK';

4 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
----------
10

Truncate

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(*) FROM emp;

COUNT(*)
----------
0

Drop
SQL> DROP TABLE emp;

Table dropped.

SQL> SELECT * FROM emp;
SELECT * FROM emp
*
ERROR at line 1:
ORA-00942: table or view does not exist