Delete vs Truncate vs Drop in SQL Server (2023)

Last updated on November 1st, 2023 at 04:36 pm

What is the Difference between Delete, Truncate, and Drop command?

The “DELETE” command removes one or more records from a table in the database, and the “TRUNCATE” command removes all rows from a table, but Still, it keeps the table structure, and the “DROP” command completely removes the entire table from the database.

Delete vs Truncate vs Drop command in SQL Server:

Here, we can see the basic differences between the Delete, Truncate, and Drop commands in SQL.

The following is the SQL script to create the Customers table with sample data. We will use this table to learn how to use delete, truncate, and drop commands.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(50) NOT NULL
);

INSERT INTO Customers (CustomerID, FirstName, LastName, Email) VALUES
(1, 'Shekh', 'Ali', 'shekh.ali@gmail.com'),
(2, 'Sadaf', 'Kamal', 'sadaf@gmail.com'),
(3, 'Jack', 'Smith', 'jacksmith@gmail.com'),
(4, 'Adam', 'Johnson', 'adamjohnson@gmail.com');

Customer Table:

What is the DELETE Command in SQL?

The DELETE command in SQL is used to delete specific rows from a table. It deletes one or more rows based on a specified condition using the WHERE clause. 

The DELETE is a DML (Data Manipulation Language) command, which can be rolled back if required.

The DELETE statement removes records from a table, but does not delete the table itself and keeps the record of each deleted row in a transaction log.

Syntax:

The following is the syntax of SQL DELETE Command:

DELETE FROM TableName WHERE condition;

Example: DELETE Command

The below SQL query will delete the row from the Customers table where the CustomerID is 3.

DELETE FROM Customers 
WHERE CustomerID = 3;

The Customer table will keep only the following records after we run the above SQL Delete query.

The Customers table has many rows. The above SQL query filters and finds the row with CustomerId 3. Then, the DELETE command removes the record of customer “Jack Smith” from the table, as that row matches the condition.

Important Facts about the SQL DELETE Command:

DELETE Command:

• The DELETE command is used in SQL to delete specific rows from a table based on a given where condition. It’s like picking out particular rows from a table and deleting them while leaving the rest records untouched.
• Imagine a table has huge records with rows and columns. When you use the DELETE command, you tell SQL which rows to find and delete based on some conditions you specify.
• For example, you can say, “Hey SQL, please delete all rows where the ‘age’ column is less than 18,” SQL will remove all rows of people under 18 from the table.
• The DELETE command is part of Data Manipulation Language (DML), which means it deals with modifying the data in the table.
• One important thing to know about the DELETE command is that it can be rolled back. It means if you accidentally delete something you didn’t mean to, you can undo the action and bring the deleted data back.
• The delete command uses the WHERE clause to delete specific rows from the table and locks each row before deletion.
• Delete command is slower than TRUNCATE as it keeps a log of deleted rows.

What is the TRUNCATE Command in SQL?

The TRUNCATE is a DDL (Data Definition Language) command in SQL that removes all the data from a table in a database. It works like a “reset” button for the table, deleting all rows while preserving its structure with the column’s name. 

Unlike the DELETE command, which removes specific rows based on conditions, TRUNCATE empties the entire table at once.

However, it’s important to note that TRUNCATE cannot be rolled back, so any data removed using TRUNCATE command is permanently deleted.

Note: The TRUNCATE command doesn’t need a WHERE clause like DELETE. It only requires the table name to delete records and performs better because it skips condition checking.

After executing the TRUNCATE command, the only thing that remains is the table structure with column’s name.

Syntax:

The syntax for the SQL TRUNCATE command is as follows:

TRUNCATE TABLE TableName;

Example: TRUNCATE Command

When you run the below SQL TRUNCATE command, it will erase all data from the Customers table, leaving only the column names. All existing rows will be instantly removed.

TRUNCATE TABLE Customers;

Important Facts about the SQL TRUNCATE Command:

TRUNCATE Command:

• The TRUNCATE command in SQL is used to remove all the data from a table completely. It’s like taking a big eraser and wiping out all the table’s contents but keeping the table’s structure (the rows and columns) untouched.
• When you use TRUNCATE, you’re telling SQL to forget about all the data stored in the table, and the table becomes empty with zero rows. It’s a quick way to eliminate lots of data in one go.
• TRUNCATE is also part of Data Definition Language (DDL), which deals with defining or altering the structure of the database.
• Unlike DELETE, TRUNCATE cannot be rolled back. Once you use TRUNCATE, the data is permanently gone. So, you must be careful while using it, as there’s no way to get the data back once it’s truncated.
• Unlike the DELETE command, TRUNCATE doesn’t support using the WHERE clause to specify specific rows for deletion.
• One significant advantage of TRUNCATE is its minimal logging in the transaction log, which makes it much faster than DELETE.
• The TRUNCATE command deallocates the data pages used to store the table data, resulting in improved performance and reduced resource usage.
• TRUNCATE resets the Identity column (if present) to its seed value, ensuring that the auto-increment feature starts from the beginning when new data is added.
• One limitation of TRUNCATE is that it cannot be used with indexed views. If the table is associated with indexed views, you must resort to DELETE instead.
• However, remember that using TRUNCATE requires the user to have ALTER permission on the table. Without this permission, the TRUNCATE command won’t execute.

What is Drop Command?

The DROP command is used to delete a table or other objects (indexes, triggers, constraints) from a database. It is a DDL ( Data Definition Language) command and it permanently removes the table and its data from the database. It cannot be rolled back.

The Drop command does not require any conditions to be specified using a ‘where’ clause, only the table name is necessary. Once the command is executed, it permanently removes the entire table structure, and the data cannot be rollback or recovered.

Syntax:

The syntax for the SQL DROP command is as follows:

DROP TABLE TableName;

Example: DROP Command

The below SQL query will delete the Customers table and all its data permanently from the database.

After a table is dropped, it cannot be recovered. The Drop command is irreversible and there is no option for rollback.

DROP TABLE Customers;

Important Facts about the SQL DROP Command:

DROP Command:

• The DROP command in SQL allows you to delete entire table or even other database objects like views or indexes. It’s like removing an entire table from the database.
• When you use the DROP command, you’re telling SQL to completely delete the table and all its data (rows and columns).
• Like TRUNCATE, DROP is also part of Data Definition Language (DDL) because it changes the database’s structure by removing objects.
• Just like TRUNCATE, the DROP command cannot be rolled back. Once you use DROP, the table and all its data are gone forever.
• So, be very cautious when using the DROP command, as you can accidentally erase important data if you’re not careful.

Remember, when working with databases, always double-check your commands and back up your data to prevent accidental loss.

References: c-sharp corner– Difference between Truncate, Delete, and Drop Command

FAQs – SQL Delete vs Truncate vs Drop:

You might want to read this too:

• Having VS Where Clause in SQL
• SQL Server Indexing: Clustered vs Non Clustered Index Explained
• Understanding COALESCE in SQL With Examples and Use Cases
• Types of Joins in SQL Server
• Function vs Stored procedure in SQL Server
• Stored Procedure in SQL Server With Examples
• Create, Alter, and Drop Database In SQL Server
• SQL Comparison Operators (Less than, Greater than, Equal, Not Equal operators)
• Primary Key Constraint In SQL Server
• View In SQL | Types Of Views In SQL Server
• SQL Server Trigger Update, Insert, Delete Examples
• SQL pivot tables: Understanding pivot tables in SQL Server

• Author
• Recent Posts

Shekh Ali

Meet Sheikh Ali, a skilled Software engineer and passionate a blogger. He enjoys sharing his expertise in C#, Java, SQL, Design Patterns, and other related topics through his writing. When he's not coding, Sheikh Ali can often indulge in his passions for reading books and fitness training.

Latest posts by Shekh Ali (see all)

• Difference Between Array And ArrayList In C#: Choosing the Right Collection - May 28, 2024
• C# Program to Capitalize the First Character of Each Word in a String - February 21, 2024
• C# Program to Find the Longest Word in a String - February 19, 2024