Difference Between Delete, Truncate And Drop Statements In SQL Server

INTRODUCTION

 
In this tutorial, I am going to explain the concept of delete, truncate, and drop statements of SQL Server with various examples. This detailed article will cover the following topics as follows:
  1. Introduction
  2. DELETE Statement
  3. TRUNCATE Statement
  4. DROP Statement
  5. Difference between Delete and Truncate Statement
  6. Difference between Delete, Truncate, and Drop Statement
  7. Conclusion
First, let's create a database with some tables containing some dummy data. Here, I am providing you with the database along with the tables containing the records, on which I am showing you the various examples. Let's see.
  1. CREATE DATABASE VENTERPRISE  
  2. GO  
  3.   
  4. USE VENTERPRISE  
  5. GO  
  6.   
  7. CREATE TABLE Employee (  
  8.     EmployeeID INT IDENTITY (100, 1) PRIMARY KEY,  
  9.     Name VARCHAR (100) NOT NULL,  
  10.     Email_ID VARCHAR (50) UNIQUE,  
  11.     ContactNumber VARCHAR (10),  
  12.     ManagerID VARCHAR(50)  
  13. );  
  14.   
  15. CREATE TABLE PRODUCT (  
  16.     DefaultID INT IDENTITY (3100,1),  
  17.     ProductID VARCHAR(50),  
  18.     Product_Name VARCHAR (100) NOT NULL,  
  19.     Brand VARCHAR(50) DEFAULT 'VATSA',  
  20.     Model_year SMALLINT NOT NULL,  
  21.     PRIMARY KEY (DefaultID)  
  22. );  
  23.   
  24. CREATE TABLE CLIENT (  
  25.     ClientID INT IDENTITY (500,1) PRIMARY KEY,  
  26.     ClientIDforExternalUse VARCHAR(50) UNIQUE,  
  27.     Name VARCHAR (100) NOT NULL,  
  28.     ContactNumber VARCHAR (10),  
  29.     Email_ID VARCHAR (50),  
  30.     Country VARCHAR (50)  
  31. );  
Now, insert some dummy data in them.
  1. INSERT INTO Employee VALUES   
  2. ('Muskan''muskan@dummymail.com'NULL'M011'),    
  3. ('Vishesh''vishesh@dummymail.com'NULL'M011'),    
  4. ('Jasmine''jasmine@dummymail.com'NULL'M111'),    
  5. ('Apar''apar@dummymail.com'NULL'M121'),    
  6. ('Riya''riya@dummymail.com'NULL'M121'),  
  7. ('Mohan''mohan@dummymail.com'NULL'M111'),    
  8. ('Shikha''shikha@dummymail.com'NULL'M011');  
  9.   
  10. INSERT INTO PRODUCT VALUES   
  11. ('V855''Laptop'DEFAULT, 2021),  
  12. ('V856''Printer'DEFAULT, 2021),  
  13. ('V857''Webcam'DEFAULT, 2021),  
  14. ('V858''Mic'DEFAULT, 2021),  
  15. ('V859''Router'DEFAULT, 2021);  
  16.   
  17. INSERT INTO CLIENT VALUES   
  18. ('C121CV''Andy'NULLNULL'United State'),  
  19. ('C122CV''Jason'NULLNULL'United Kingdom'),  
  20. ('C123CV''Smith'NULLNULL'Ukrain'),  
  21. ('C124CV''Kumud'NULLNULL'India'),  
  22. ('C125CV''User'NULLNULL'Germany');  
By using the following query, let's check our following tables.
 
1) To get data from the "Employee" table, use the following query.
  1. SELECT * FROM VENTERPRISE..Employee  
select query 1
 
2) To get data from the "PRODUCT" table, use the following query.
  1. SELECT * FROM VENTERPRISE..PRODUCT  
select query 2
 
3) To get data from the "CLIENT" table, use the following query.
  1. SELECT * FROM VENTERPRISE..CLIENT  
select query 3
 

Delete Statement

 
DELETE is a DML (Data Manipulation Language) command and is used to delete one or more rows (records) from a table (relation). DELETE is used only to remove data from the table, not to remove a table from the database.
 
If you want to delete only a few rows and not the table structure, use the DELETE command. It can delete some or all rows (records) from the table (relation). After execution, the DELETE statement returns the total number of rows removed from the table.
 
The DELETE query may contain a "WHERE" clause. If you want to delete some specific records from the table, then use the "WHERE" clause. And, if you want to delete all the data from the table, just use DELETE, followed by the table name.
 
Error Handling for DELETE Statement
 
By defining a statement or query in a TRY and CATCH construct, the user can implement error handling for the DELETE statement.
 
If you try to delete a row that is referenced by data in another table with a FOREIGN KEY constraint, the DELETE statement may fail. The statement is canceled, an error has occurred, and no rows are deleted if DELETE removes multiple rows and any of the deleted rows violate the constraint.
 
Key Points
  • DELETE is a DML command.
  • DELETE can be executed by triggers.
  • DELETE query may contain a WHERE clause to delete specific rows.
  • DELETE doesn't reset auto-increment.
  • To use the DELETE statement, the user must have DELETE permission for that table.
  • During execution, the DELETE statement uses a row lock, which locks each row in the table with each row deletion.
  • The DELETE statement deletes rows one by one and records each removed row in the transaction log.
  • DELETE statement maintains the log, so it can be rolled back in SQL Server.
  • The DELETE statement returns the number of rows (records) that were deleted by its execution.
  • DELETE is slower than TRUNCATE.
Syntax
 
DELETE FROM <table_name>
WHERE <condition>;
 

Examples

 
The examples in this section demonstrate the functionality of the DELETE statement. Let's see.
 
1) USING the WHERE Clause to delete a set of rows
 
The following example deletes all rows from the "Employee" table in the "VENTERPRISE" database in which the value of the "ManagerID" column is "M011".
  1. DELETE FROM Employee  
  2. WHERE ManagerID = 'M011'  
DELETE Example 1
 
2) USING DELETE with no WHERE Clause
 
The following example deletes all rows from the "Employee" table in the "VENTERPRISE" database.
  1. DELETE FROM Employee;  
  2. Print 'Hey, Your all records have been deleted from this table'
DELETE Example 2
 
Note 
If you don’t specify the WHERE condition in the DELETE statement then, it will delete all the rows from the table.
 
3) SQL Server Delete Transaction Example
 
a) Execute the following statement(s) to begin the transaction.
  1. BEGIN TRANSACTION  
  2. SELECT * FROM Employee  --to check the records
  3. DELETE FROM Employee  
  4. WHERE ManagerID = 'M011'
DELETE Example 3 query 1
 
b) Now, let's check our result.
  1. SELECT * FROM Employee  
DELETE Example 3 query 2
 
c) Let's try to roll back it by executing the following statement(s).
  1. ROLLBACK TRANSACTION  
DELETE Example 3 query 3
 
d) Again, let's check our result. And yes, we have rolled back out delete transactions.
  1. SELECT * FROM Employee  
DELETE Example 3 query 4
 

TRUNCATE Statement

 
TRUNCATE is a DDL (Data Definition Language) command that is used to delete all data from a table (relation) without removing the table structure. TRUNCATE is used only to remove data from the table, not to remove a table from the database. This is almost similar to the DELETE statement because it does not have a WHERE clause.
 
Key Points
  • TRUNCATE is a DDL Command.
  • WHERE Clause cannot be used with TRUNCATE.
  • TRUNCATE resets auto-increment.
  • To use the TRUNCATE, you need permission to use the TRUNCATE TABLE.
  • TRUNCATE removes all records from a table by using a table lock.
  • TRUNCATE keeps the minimum log space in the transaction log, so it can also be rolled back in SQL Server.
  • In SQL Server, TRUNCATE operation can be rolled back.
  • TRUNCATE does not return the number of deleted rows (records) from the table as a result.
  • TRUNCATE is faster than DELETE.
Note 
Users cannot use the TRUNCATE TABLE command on tables that are referenced by the FOREIGN KEY constraint. But, the user can truncate a table that has a foreign key that references itself.
 
Syntax
 
TRUNCATE TABLE <table_name>;
 

Examples

 
The examples in this section demonstrate the functionality of the TRUNCATE statement.
 
1) Execute the following TRUNCATE statement to remove all the data (truncate) from the "PRODUCT" table.
  1. TRUNCATE TABLE PRODUCT  
TRUNCATE Example 1
 
2) SQL Server TRUNCATE Transaction Example
 
a) Execute the following statement(s) to begin the transaction.
  1. BEGIN TRANSACTION  
  2. SELECT * FROM PRODUCT   --to check the records
  3. TRUNCATE TABLE PRODUCT 
TRUNCATE Example 2 query 1
 
b) Now, let's check the result.
  1. SELECT * FROM PRODUCT  
TRUNCATE Example 2 query 2
 
c) Let's try to roll back it by executing the following statement(s).
  1. ROLLBACK TRANSACTION  
TRUNCATE Example 2 query 3
 
d) Again, let's check our result. And yes, we have rolled back our truncate statements.
  1. SELECT * FROM PRODUCT  
TRUNCATE Example 2 query 4
 

DROP Statement

 
DROP TABLE is also a DDL (Data Definition Language) command. It is used to remove data stored in a table as well as a table structure from a database. The DROP TABLE statement removes the table's structure, data, indexes, constraints, and triggers. When a table is dropped, any constraints or triggers associated with it, are also dropped.
 
A table that is referenced by a FOREIGN KEY constraint cannot be dropped using the DROP TABLE statement. The referencing FOREIGN KEY constraint or the referencing table must be dropped first. The referencing table must be specified first, if both the referencing table and the primary key holding table are being dropped in the same DROP TABLE statement.
 
Key Points
  • DROP Statement is also a DDL Command.
  • In SQL Server, DROP table operation can be rolled back.
  • The DROP statement frees up memory space (table space from the memory).
Syntax
 
DROP TABLE <table_name>;
 
Note 
Multiple tables can be dropped in any database using a comma-separated list.
 
Syntax
 
DROP TABLE (table_name1, table_name2, ...);
 

Examples

 
The examples in this section demonstrate the functionality of the DROP statement. Let's see.
 
1) Execute the following DROP statement to drop the table from the database.
  1. DROP TABLE CLIENT  
  2. Print 'Hey, you have dropped your table...' 
DROP Example 1
 
2) SQL Server DROP Transaction Example
 
a) Execute the following statement(s) to begin the transaction.
  1. BEGIN TRANSACTION  
  2. SELECT * FROM CLIENT   --to check the records
  3. DROP TABLE CLIENT 
DROP Example 2 query 1
 
b) Now, let's check the result.
  1. SELECT * FROM CLIENT 
DROP Example 2 query 2
 
c) Let's try to roll back it by executing the following statement(s).
  1. ROLLBACK TRANSACTION  
DROP Example 2 query 3
 
d) Again, let's check our result. And yes, we have rolled back our delete statement.
  1. SELECT * FROM CLIENT
DROP Example 2 query 4
 

Difference between DELETE and TRUNCATE Statements

 
Now, let's see the difference between DELETE and TRUNCATE Command.
 
S.No DELETE TRUNCATE
1 DELETE is a DML Command. TRUNCATE is a DDL Command. 
2 DELETE is used to delete the records based on a condition. TRUNCATE is used to remove all the records from a table.
3 WHERE Clause can be used with Delete to filter the records. WHERE Clause can not be used with Truncate.
4 The DELETE statement deletes rows one by one and records an entry for each deleted row in the transaction log. TRUNCATE keeps the minimum log space in the transaction log.
5 DELETE uses a row lock. TRUNCATE uses a table lock.
6 DELETE does not reset auto-increment. TRUNCATE resets auto increment.
7 In SQL Server, DELETE can be rolled back. In SQL Server, TRUNCATE can also be rolled back.
8 DELETE is slower than TRUNCATE. TRUNCATE is faster than DELETE.
 

Difference between DELETE, TRUNCATE, and DROP Statements

 
Let's look at the quick difference between DELETE TRUNCATE, and DROP Statements.
 
S.No. Key Points DELETE TRUNCATE DROP
1 Classification DML (Data Manipulation Language) DDL (Data Definition Language) DDL (Data Definition Language) 
2 Use to delete one or more rows all the rows all the data stored in a table along with its structure 
3 WHERE Condition? Yes No No
4 Uses a lock Row Lock Table Lock Table Lock
5 Write Transaction log for each row for the whole table for the whole table
6 Should be Rollback? Yes Yes Yes
7 Does it reset the auto-increment? No Yes ---
8 Speed Slower than TRUNCATE Faster than DROP Quick to perform (faster)
 
Reference
 
https://docs.microsoft.com/en-us/sql/t-sql/statements/
 

CONCLUSION

 
In this article, we have discussed the concept of delete, truncate, and drop statements of SQL Server with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about SQL Server.
 
Thanks for reading.