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

Another major difference is that in DELETE it will traverse/scan to all(conditional based on predicate) the records and does delete. But in drop it will traverse records instead it will remove the actual table entry from database. If we look at execution plan for Delete:

In case of Drop as it is DDL command it will not even traverse records and will not create any execution plan.

Answer 2

One of these performs a delete, the other provides you with the TSQL script to do a delete so you can modify or use it elsewhere.

Answer 3

In the delete object GUI, on top there is a 'script' option which gives the t-sql statement which is plain drop table statement. Where as when you go for the drop table option, the t-sql generated would perform drop only if the table exists in the sys.objects table.

Answer 4

Drop table will delete the content of the table and the table self. Delete table will just drop the content. Truncate will just 'reset' the content to zero

Answer 5

Those two are the same operations. DROP TABLE is SQL statement for this, Delete is standard, user-friendly, menu-driven command name. That's all.

Answer 6

We can delete the particular record from a table with using delete command . but using drop we can drop the tables. if you use Drop the structure will also go, but if you use delete command the structure won't go.