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.

Drop Table

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

DROP TABLE ProductVendor1 ;

 

This example creates a temporary table, tests if it exists, drops it, and tests again if it exists

USE AdventureWorks;

GO

CREATE TABLE #temptable (col1 int);

GO

INSERT INTO #temptable

VALUES (10);

GO

SELECT * FROM #temptable;

GO

IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL

DROP TABLE #temptable;

GO

--Test the drop.

SELECT * FROM #temptable;

 

 

Delete Table

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

USE AdventureWorks2008R2;

GO

DELETE FROM Production.ProductCostHistory

WHERE StandardCost > 1000.00;

GO

 

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.

USE AdventureWorks2008R2;

GO

DELETE TOP (2.5) PERCENT

FROM Production.ProductInventory;

GO