Menu
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.
989
DELETE is a Data Manipulation Language command, DML command and is used to remove tuples/records from a relation/table. Whereas DROP is a Data Definition Language, DDL command and is used to remove named elements of schema like relations/table, constraints or entire schema.
The TRUNCATE command is a Data Definition Language Command. The DELETE command deletes one or more existing records from the table in the database. The DROP Command drops the complete table from the database. The TRUNCATE Command deletes all the rows from the existing table, leaving the row with the column names.
DELETE command is used to remove some or all the tuples from the table. On the other hand, the DROP command is used to remove schema, table, domain or Constraints from the database. DELETE is a Data Manipulation Language command whereas, DROP is a Data Definition Language command.
When SQL Server drops a table, it also deletes all data, triggers, constraints, permissions of that table. Moreover, SQL Server does not explicitly drop the views and stored procedures that reference the dropped table.
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.
135
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
