Difference Between Truncate and Delete



Sno.
Truncate
Delete
1
It is a DDL command
It's a DML command
2
DDL because it use schema lock
DML because it uses row level lock usually
3
Truncate doesn't support from and where clause
Delete supports from and where clause
4
syntax: Truncate table Emp
Syntax: Delete from table
Syntax: Delete from emp where id =10
5
Truncate is minimally logged operation makes it faster
It is fully logged operation makes it comparatively slow
6
It doesn't activate the table's after delete trigger.
It activate the table's after delete trigger
7
This resets identity key values
It doesn't impact identity key values
8
Truncate cant be roll back in transaction
Delete can be rolled back
9
You can't use TRUNCATE TABLE where the tables are referenced by a foreign key constraint
Delete can be used when the tables are referenced by a foreign key constraint
10
You can't use TRUNCATE TABLE where the tables participate in an indexed view
Delete can be used when the tables participate in an indexed view
11
You can't use TRUNCATE TABLE where the tables participate in transactional replication
Delete can be used when the tables participate in transactional replication
12
You can't use TRUNCATE TABLE where the tables participate in  merge replication.
Delete can be used when the tables participate in  merge replication.
13
It free the space containing the table.
It does not free the space containing the table.
14
TRUNCATE removes the data by deallocating the data pages
Delete just remove the data from pages / Pages
15
Truncate cannot be issued over a database link in Oracle
Delete can be issued over a database link
16
Truncate  generates a good amount of redo transactions only
Delete generates a good amount of redo and undo transactions

No comments:

Post a Comment