Wha开发者_JAVA百科t is the difference between drop and delete database?

---

Difference between DELETE and DROP commands

Delete: The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

Drop: The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

---

Simply: a DELETE command removes matching rows, a DROP command removes the entire table.

---

I know this is an old post, but I was looking for the answer myself, and since I believe the OP has asked about dropping or deleting the database, rather than tables or content, I thought I'd chip in.

Both will delete the database, but both could involve more than one step if you want to completely remove the database and associated artefacts.

DELETE

If you are using SQL Server Management Studio, you can delete the database by right clicking on the database and selecting Delete. The resulting dialog offers a couple of checkboxes :

1. 'Delete backup and restore history information for databases'
2. 'Close Existing connections'

If you don't tick 'Delete backup and restore history..' , those files will remain on the server unless you manually get rid of them. 'Close Existing connections' is a good idea, otherwise you may get an error telling you the database is still in use (even if it's just you, while you're trying to delete it)

DROP

The SQL Command 'DROP' alone will not remove everything. You will still need to also remove the backup history, and set the database to 'single user mode' - or it may complain the database is still in use, as above.

--Remove backup history
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'YourDBName'
GO
USE [master]
GO
--close all the open connections to your database
ALTER DATABASE [YourDBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
--remove the actual database 
DROP DATABASE [YourDBName]
GO

---

Delete removes content and drop the structure of a database.

---

Delete removes content of table.Drop removes content and structure of table.Delete can be rolled back but Drop can not be rolled back

---

Suppose you are using MS SQL Then if you want to delete the whole Table then you would use:

DROP TABLE MyTable

This would delete the whole table and its constraints

To delete any specific row you would use:

DELETE FROM MyTable WHERE id=5

This would delete the row with the id = 5 If no conditions are matched it would delete all rows

---

DROP removes the entire table and associated objects from the catalog, requiring you to create all the indexes and constraints from scratch.

---

Drop in a function that physically removes and particular table or a column. The table struction remails same. If you want to see the structure, you can write this desc tablename; and the structure will be same after dropping the table. Delete function permanantly deletes the table or a column, the structure of the table also.