Delete, truncate, drop command in SQL

Question

What is difference delete , truncate and drop command in SQL.

Dhara Maheshwari · Answer

Delete command when used with where condition only deletes that particular row, Truncate command deletes all the rows from table, but it doesn't delete the structure of table only the content of table, drop command will remove the whole table in this table structure will also be deleted. The priviledges, indexes created on table will also be deleted.. Drop and Truncate are DDL commands while Delete is DML command.. We can't rollback using Truncate , but we can rollback using DELETE .. Truncate is faster as compared to delete U may also refer to below link: http://www.orafaq.com/faq/difference_between_truncate_delete_and_drop_commands

Akkiraju Ivaturi · Answer

Drop table deletes all the data, indexes, triggers, constraints and permission specifications for the tables. Any views or stored procedures depending on the dropped table must be explicitly dropped. Delete removes rows from the table if a criteria is specified otherwise deletes all the rows. If it encounters foreign key constraint, it gets cancelled and will not delete row. it logs the individual row deletion. Truncate deletes all the rows in the table and it does not log every row delete and so it is faster.

R ACHUTHA · Answer

delete and truncate commands delete the table data but not the schema of the table. And delete command did not reset the identity value of the table. Truncate command reset the identity value of table that means if you truncate a table then if there is any identity value in the table it will reset to the initial value. Drop command delete the entire table data and table schema.

Delete, truncate, drop command in SQL

Answers (3)