For many days i had a question in mind regarding Difference between Truncate and Delete
Here am listing the differences
Delete:
1)Rollback operation can be performed on delete queries
2)"where"clause can be used on delete queries
Truncate:
1)Rollback operation can not be performed on Truncate queries
2)"where" clause can not be used on Truncate queries
And most imported point about Truncate is truncate drops the table structure and recreates the table structure .
For example we may have a identity coulmn which is autoincremented (say empid),when there are 100 records in table
1)when we delete all the 100 records using "delete" query ,it deletes all the records but when we try to insert the new record the autoincremented value(empid) will be 101.
2) For the same scenario if we use truncate query to delete all 100 records.When we try to insert to insert new record the autoincremented value(empid) will be 1.
Here am listing the differences
Delete:
1)Rollback operation can be performed on delete queries
2)"where"clause can be used on delete queries
Truncate:
1)Rollback operation can not be performed on Truncate queries
2)"where" clause can not be used on Truncate queries
And most imported point about Truncate is truncate drops the table structure and recreates the table structure .
For example we may have a identity coulmn which is autoincremented (say empid),when there are 100 records in table
1)when we delete all the 100 records using "delete" query ,it deletes all the records but when we try to insert the new record the autoincremented value(empid) will be 101.
2) For the same scenario if we use truncate query to delete all 100 records.When we try to insert to insert new record the autoincremented value(empid) will be 1.
One more difference
ReplyDelete1. Truncate works faster than delete.
While using truncate logs wont be generated.
But for delete logs will generate.
Just a update on your point
1)Rollback operation can not be performed on Truncate queries
we can perform the rollback but data wont come back.
-- Dileep
Ya thanks for sharing your valuable points dileep
ReplyDelete