In SQL server, you have three keywords – Drop, Delete and Truncate; which might be confusing to beginners. Let’s understand what’s the difference between these and where to use which one.
Drop: Both table structure and table rows can be deleted.
DROP TABLE TableName;
Delete: Table structure is not deleted while rows can be selectively deleted.
DELETE FROM TableName WHERE Condition;
Truncate: Table structure is not deleted while all the records are deleted at once.
TRUNCATE TABLE TableName
So you got basic functionality of Drop, Delete and Truncate now. Here are some more important points to be noted about these-
- DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.
- All the statements (Delete, Truncate and Drop) are logged operations, but the amount of information that is logged varies. Delete statement logs an entry in the transaction log for each deleted row, whereas Truncate Table logs only the Page de-allocations. Hence, truncate is a little faster than Delete.
- We can have a where clause in Delete statement whereas Truncate statement cannot have a where clause.
Example of rollback with Truncate –
Truncate Table tblProduct
Select * from tblProduct ---no data
Select * from tblProduct --data is there after rollback.