What is the difference between drop and delete database?

Question

What is the difference between drop and delete database?

Answer 1

Difference between DELETE and DROP Commands

Delete: The DELETE command is used to delete rows from a table. WHERE can only be used to delete some lines. If no WHERE clause is specified, all rows will be deleted. After the DELETE operation is complete, you need to execute a COMMIT or ROLLBACK transaction to make permanent changes or cancel it. Please note that this operation will remove DELETE triggers on the table to run.

Drop: The DROP command deletes a table from the database. All tables of rows, indexes and privileges are also deleted. DML triggers will not be fired. The operation cannot be performed backward.

Answer 2

Simple: the DELETE command deletes the corresponding rows, the DROP command deletes the entire table.

Answer 3

Delete deletes the contents and deletes the database structure.

Answer 4

Delete deletes the contents of the table. Drop deletes the contents and structure of the table. You can delete the rollback, but the fall strong> can not rollback

Answer 5

Delete

• Delete delete only data, table structure remains intact.
• This is a DML expression
• Possible return back
• No commit is performed either before or after. (Because it is a DML expression).
• They take locks in rows,
• They generate repeat (a lot)
• They require segments in the UNDO tablespace.
• Deletion does not free up segment space; therefore, the table in which all records were deleted retains all of its original blocks.
• It can activate triggers.

A fall

• A drop permanently deletes both the data and the structure of the table.
• This is a DDL expression
• Rollback not possible
• It issues a COMMIT before it acts, and another COMMIT after that, so the transaction cannot be rolled back. (Because it is a DDL expression)
• No row level locks are performed.
• No replay or rollback is generated.
• They do not require segments in the UNDO tablespace.
• All extents boundaries, the first of which are selected from the table
• It does not activate triggers.

Answer 6

Suppose you use MS SQL. Then, if you want to delete the entire Table , you should use:

DROP TABLE MyTable

This will delete the entire table and its limitations.

To remove any specific line that you would use:

REMOVE FROM MyTable WHERE id = 5

This will delete the row with id = 5. If no conditions match, it will delete all rows

Answer 7

I know this is an old post, but I myself was looking for an answer, and since I believe that the OP asked about deleting or deleting the database, and not about tables or content, I thought I would turn it on.

Both will delete the database, but both can include more than one step if you want to completely delete the database and related artifacts.

Delete

If you are using SQL Server Management Studio, you can delete the database by right-clicking on the database and selecting Delete. As a result, the dialog offers a couple of flags:

• 'Delete backup and restore history information for databases
• 'Close existing connections

If you do not check "Delete backup and recovery history", these files will remain on the server if you do not get rid of them manually. "Close existing connections" is a good idea, otherwise you may receive an error message when you are still using the database (even if you are just trying to delete it)

Drop

The SQL "DROP" command alone will not delete everything. You still need to delete the backup history and set the database to “one user mode” - or it may complain that the database is still in use, as described 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 

Answer 8

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

Answer 9

Drop a function that physically deletes a specific table or column. The column corrects the same. If you want to see the structure, you can write this desc tablename; and the structure will be the same as after the table. The delete function permanently deletes a table or column, as well as the structure of the table.