What is the difference between drop table and dele

问题:

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.

回答1:

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.

回答2:

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.

回答3:

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.

回答4:

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.

回答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.

回答6:

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.

回答7:

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.

回答8:

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.

回答9:

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

回答10:

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.

回答11:

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

回答12:

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.