SQLTeam.com | Weblogs | Forums

What is the difference between DROP and TRUNCATE commands?

can anyone tell me about the difference between DROP and TRUNCATE in SQL?

drop can delete a table
truncate empties a table of all rows

DROP {table} completely removes the table from the system. All column definitions, indexes, defaults, constraints, triggers, etc., are removed / destroyed.

TRUNCATE {table} removes all the data rows from the table, but does not remove the table definition, so everything mentioned above is still in the db.

TRUNCATE: With truncate command we can delete all the rows of table at one time, there is no need to remove one by one row or column from table. But truncate won't allow to delete single row from the table because it don't have WHERE clause.

DROP: We can delete whole table with all its data and name with single command.
Both commands are of Data definition Language Command (DDL) and cannot be restored from any command.
Syntax: DROP table;

1 Like