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:

Introduction
DELETE Statement
TRUNCATE Statement
DROP Statement
Difference between Delete and Truncate Statement
Difference between Delete, Truncate, and Drop Statement
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.

CREATE DATABASE VENTERPRISE
GO
USE VENTERPRISE
GO
CREATE TABLE Employee (
EmployeeID INT IDENTITY (100, 1) PRIMARY KEY,
Name VARCHAR (100) NOT NULL,
Email_ID VARCHAR (50) UNIQUE,
ContactNumber VARCHAR (10),
ManagerID VARCHAR(50)
);
CREATE TABLE PRODUCT (
DefaultID INT IDENTITY (3100,1),
ProductID VARCHAR(50),
Product_Name VARCHAR (100) NOT NULL,
Brand VARCHAR(50) DEFAULT 'VATSA',
Model_year SMALLINT NOT NULL,
PRIMARY KEY (DefaultID)
);
CREATE TABLE CLIENT (
ClientID INT IDENTITY (500,1) PRIMARY KEY,
ClientIDforExternalUse VARCHAR(50) UNIQUE,
Name VARCHAR (100) NOT NULL,
ContactNumber VARCHAR (10),
Email_ID VARCHAR (50),
Country VARCHAR (50)
);

Now, insert some dummy data in them.

INSERT INTO Employee VALUES
('Muskan', '[email protected]', NULL, 'M011'),
('Vishesh', '[email protected]', NULL, 'M011'),
('Jasmine', '[email protected]', NULL, 'M111'),
('Apar', '[email protected]', NULL, 'M121'),
('Riya', '[email protected]', NULL, 'M121'),
('Mohan', '[email protected]', NULL, 'M111'),
('Shikha', '[email protected]', NULL, 'M011');
INSERT INTO PRODUCT VALUES
('V855', 'Laptop', DEFAULT, 2021),
('V856', 'Printer', DEFAULT, 2021),
('V857', 'Webcam', DEFAULT, 2021),
('V858', 'Mic', DEFAULT, 2021),
('V859', 'Router', DEFAULT, 2021);
INSERT INTO CLIENT VALUES
('C121CV', 'Andy', NULL, NULL, 'United State'),
('C122CV', 'Jason', NULL, NULL, 'United Kingdom'),
('C123CV', 'Smith', NULL, NULL, 'Ukrain'),
('C124CV', 'Kumud', NULL, NULL, 'India'),
('C125CV', 'User', NULL, NULL, 'Germany');

By using the following query, let's check our following tables.

1) To get data from the "Employee" table, use the following query.

SELECT * FROM VENTERPRISE..Employee

2) To get data from the "PRODUCT" table, use the following query.

SELECT * FROM VENTERPRISE..PRODUCT

3) To get data from the "CLIENT" table, use the following query.

SELECT * FROM VENTERPRISE..CLIENT

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".

DELETE FROM Employee
WHERE ManagerID = 'M011'

2) USING DELETE with no WHERE Clause

The following example deletes all rows from the "Employee" table in the "VENTERPRISE" database.

DELETE FROM Employee;
Print 'Hey, Your all records have been deleted from this table'

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.

BEGIN TRANSACTION
SELECT * FROM Employee --to check the records
DELETE FROM Employee
WHERE ManagerID = 'M011'

b) Now, let's check our result.

SELECT * FROM Employee

c) Let's try to roll back it by executing the following statement(s).

ROLLBACK TRANSACTION

d) Again, let's check our result. And yes, we have rolled back out delete transactions.

SELECT * FROM Employee

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.

TRUNCATE TABLE PRODUCT

2) SQL Server TRUNCATE Transaction Example

a) Execute the following statement(s) to begin the transaction.

BEGIN TRANSACTION
SELECT * FROM PRODUCT --to check the records
TRUNCATE TABLE PRODUCT

b) Now, let's check the result.

SELECT * FROM PRODUCT

c) Let's try to roll back it by executing the following statement(s).

ROLLBACK TRANSACTION

d) Again, let's check our result. And yes, we have rolled back our truncate statements.

SELECT * FROM PRODUCT

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.

DROP TABLE CLIENT
Print 'Hey, you have dropped your table...'

2) SQL Server DROP Transaction Example

a) Execute the following statement(s) to begin the transaction.

BEGIN TRANSACTION
SELECT * FROM CLIENT --to check the records
DROP TABLE CLIENT

b) Now, let's check the result.

SELECT * FROM CLIENT

c) Let's try to roll back it by executing the following statement(s).

ROLLBACK TRANSACTION

d) Again, let's check our result. And yes, we have rolled back our delete statement.

SELECT * FROM CLIENT

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.

2 Comments

Join the conversation! Your thoughts help the community grow.

Bohdan Stupak5y ago
I've enjoyed the article. You've mentioned not only obvious performance difference but also lesser-known facts. Great job!
- Onkar Sharma5y ago
  I'm glad you enjoyed it. People like you keep motivating us to give our best. Thanks for the appreciation...