What is the difference between drop table and dele

Question

In SQL Server, what is the difference between the following two-

• Right click on a database object (table/view) and opt for Drop table (i.e. Script table as -> DROP To -> New Query Editor Window)

• Right click on a database object (table/view) and opt for Delete.

I tried them both and both perform the same action. Any reason for having two options for the same thing? Is the Delete option just a crude way of dropping the DB object?

Just for the record - I'm using SS2008.

Answer 1

Delete v/s Drop.

• Delete statement performs conditional based deletion, whereas Drop command deletes entire records in the table.

• Delete statement removes only the rows in the table and it preserves the table structure as same, and Drop command removes all the data in the table and the table structure.

• Delete operation can be rolled back and it is not auto committed, while Drop operation cannot be rolled back in any way as it is an auto committed statement.

Answer 2

Drop table..it will delete complete table from the Database.it can not retrieved bak Delete is used to deleting data from the table.. data can be retrieved using ROLL BACK.

Answer 3

Using Delete command rows can be deleted from table conditionally, but changes can be done/undone by using COMMIT and ROLLBACK. Table structure is never lost. Truncate is an alternative of Delete in the sense that it allows Deletion of rows without losing the table structure but UNLIKE Delete, operation can't be undone. Drop statement however, removes the table completely along with its structure from the database and this operation can't be undone. So, Delete is a DML statement and Turncate and Drop are DDL statements.

Answer 4

DROP will delete all data and the table structure as well.

DELETE will delete the data but the table structure will remain the same and we can still rollback the data. Also with DELETE you can use the where condition i.e. to delete only certain records.

Answer 5

it is drop table and delete object, at least in SQL Server 2005. Both perform the same action.

Delete table and Drop table are not the same though. The former will delete all data from the table whilst the latter will remove the table from the database.

Answer 6

If you choose Delete, then choose to script it, you will see that it gives you a drop table statement as well.