What is the difference between drop table and delete table in SQL Server
Difference between drop table and delete table in SQL Server
You will notice that there are two ways to get rid of data you don’t require in SQL Server. One is by the Delete Table command and the other one is by the Drop table command. So what is the difference between the two?
Delete Table and Drop Table command are not the same. Delete Table will delete all the data in the table, but you still have an empty table. Whereas, Drop Table will delete the data and also drop the table so you no longer have even an empty table.
If you use the Drop table it will delete the table and you cannot retrieve it back using RollBack. But if you used the Delete Table, you can get back the data using ROLLBACK.
Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables.
Any view or stored procedure that references the dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE
When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints
This example removes the table named ProductVendor1 and its data and indexes from the current database
This example creates a temporary table, tests if it exists, drops it, and tests again if it exists
Delete Removes rows from a table or view.
If you delete all rows in a table by using DELETE tablename statement, the table exists until it is dropped.
Here is an example of how to use Delete Table
The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed
If you want to delete all the rows in a table, use the DELETE statement without specifying a WHERE clause
This example deletes 2.5 percent of the rows (27 rows) in the ProductInventory table.