Hi Friends, in this article I will explain about the Difference
between TRUNCATE, DELETE and DROP commands in SQL SERVER.For many interviews this question is frequently asked.
TRUNCATE
Command removes all the data or all rows
from a table.
· We cannot use where clause with Truncate command.
· The operation cannot be rolled back and no triggers will be fired because it does not log individual rows.
· As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
· It frees the space containing the table.
· Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
· This is a DDL command as its resets identity columns, deallocates data pages and empties them for use of other objects in the database.
First of all we know those individually after that we will
know differences.
Truncate:
· We cannot use where clause with Truncate command.
· The operation cannot be rolled back and no triggers will be fired because it does not log individual rows.
· As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.
· It frees the space containing the table.
· Identity columns are re-seeded on this operation, if no seed was defined then the default value 1 is used.
· This is a DDL command as its resets identity columns, deallocates data pages and empties them for use of other objects in the database.