Delete vs Truncate vs Drop in SQL Server: What is the Difference between DELETE, TRUNCATE and DROP in SQL?

In SQL Server, the Delete, Truncate and Drop commands are used to delete or remove data from a table. Despite the fact that these commands are often used interchangeably, they have distinct implications and consequences. We will look at the differences between delete, truncate, and drop commands and how to use them correctly In this article.

delete-vs-truncate-vs-drop-command-in-sql
Delete vs Truncate vs Drop command in SQL

Difference between DELETE, TRUNCATE, and DROP commands

The following are the main differences between DELETE, TRUNCATE, and DROP commands in SQL:

  • DELETE: The DELETE command is used to delete specific rows from a table based on a specified condition. The delete command is a DML (Data Manipulation Language) command and it can be rolled back if needed.
  • TRUNCATE: The TRUNCATE command in SQL is used to remove all the data from a table, but unlike the delete command, it cannot be rolled back. This is a DDL (Data Definition Language) command, which means it is used to define the structure of a database or its objects.
  • DROP: The DROP command in SQL is used to delete a table or other database objects. It is a DDL command that deletes the table and its data from the database permanently. It cannot be rolled back.
  • Logging: The DELETE command logs the data for each row that is deleted, on the other hand, the TRUNCATE and the DROP commands don’t log the data as they are DDL commands.
  • Foreign key constraints: When deleting data, the DELETE command checks foreign key constraints. On the other hand, TRUNCATE and DROP commands do not check for foreign key constraints.
  • Identity column: The identity column cannot be reset by a DELETE command, but it can be reset by TRUNCATE and DROP.

The following is the SQL code to create the “Customers” table with sample data. We will use this table to learn 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');
CustomerIDFirstNameLastNameEmail
1ShekhAlishekh.ali@gmail.com
2SadafKamalsadaf@gmail.com
3JackSmithjacksmith@gmail.com
4AdamJohnsonadamjohnson@gmail.com
Customers Table

What is the DELETE Command in SQL?

The DELETE command in SQL is used to delete specific rows from a table. It is used to delete one or more rows based on a specified condition using the WHERE clause. The delete is a DML (Data Manipulation Language) command and it 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 syntax for the SQL DELETE command is as follows:

DELETE FROM TableName WHERE condition;

DELETE Example

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

DELETE FROM Customers 
WHERE CustomerID = 3;

The table shown below will remain after the execution of the above Delete SQL query.

CustomerIDFirstNameLastNameEmail
1ShekhAlishekh.ali@gmail.com
2SadafKamalsadaf@gmail.com
4AdamJohnsonadamjohnson@gmail.com

The Customers table contains multiple rows. The above SQL query will help the user to filter specific rows that need to be deleted. The WHERE clause helps us find the row for the Customer whose CustomerId is 3. The DELETE command is then used to delete the row returned by the WHERE clause. In the above SQL query, the DELETE command deletes the record of customer Jack Smith from the table whose CustomerId is 3.

Key points about the DELETE Command

  • DELETE is a DML (Data Manipulation Language) command.
  • DELETE is a command used to delete rows from a table
  • It locks each row before deletion
  • You can use the WHERE clause to delete specific rows
  • It’s slower than TRUNCATE as it keeps a log of deleted rows
  • It removes rows one at a time
  • It keeps the identity of a column
  • You need permission to use DELETE on a table
  • It uses more space than TRUNCATE
  • It can be used with indexed views.

What is the TRUNCATE Command in SQL?

The TRUNCATE command is used to delete all data from a table, but unlike the DELETE command, it cannot be rollback. This command is a part of DDL (Data Definition Language), which is used to define the structure of a database or its objects. It removes all data from a table without deleting it from the database.

It doesn’t use a WHERE clause like the DELETE command and only requires the table name to delete the records. It also has better performance as it skips the checking of conditions.

After executing the TRUNCATE command, the only thing that remains is the column names of the table.

Syntax

The syntax for the SQL TRUNCATE command is as follows:

TRUNCATE TABLE TableName;

TRUNCATE Example

The following SQL query will completely clear the data from the Customers table, leaving only the column names remaining. All existing rows will be immediately removed upon execution of this query.

TRUNCATE TABLE Customers;

Key points about the TRUNCATE command

  • TRUNCATE is a DDL command
  • TRUNCATE locks the whole table and removes all records.
  • You can’t use the WHERE clause with the TRUNCATE command.
  • TRUNCATE has minimal logging in the transaction log, making it faster.
  • TRUNCATE deallocates the data pages used to store the table data.
  • It will reset the Identity column to its seed value.
  • Required ALTER permission on the table to run the TRUNCATE command.
  • Truncate uses less transaction space than the Delete statement.
  • Truncate can not be used with indexed views
  • TRUNCATE is faster than DELETE as it skips the checking of where conditions.

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 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;

DROP Example

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;

Key points about the DROP Command

  • The DROP command is used to delete a table from the database completely
  • When using DROP, all the rows, indexes, and privileges of the table will also be deleted
  • DML triggers will not be activated when using the DROP command
  • Once a table is dropped, the action cannot be undone
  • DROP and TRUNCATE are types of commands used in Data Definition Language (DDL) while DELETE is a command used in Data Manipulation Language (DML)
  • DELETE command allows undoing the deletion while DROP and TRUNCATE cannot be undone.

Comparison Table: Delete vs Truncate vs Drop in SQL Server

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

ParameterDELETEDROPTRUNCATE
Command Type:DML (Data Manipulation Language)DDL (Data Definition Language)DDL (Data Definition Language)
Purpose:Used to delete specific rows from a table based on a where condition.Used to delete a table or other database objects.Used to remove all data from a table.
Rollback:The DELETE command allows for the restoration of deleted data through the use of “ROLLBACK”. If a row is deleted from the database, it can be retrieved again using this command.Can not be rolled back.Can not be rolled back.
Logging:Logs the data for each row that is deleted.It doesn’t log the data.It doesn’t log the data.
Foreign key constraints:Checks the foreign key constraints while deleting.It doesn’t check the foreign key constraints.It doesn’t check the foreign key constraints.
Identity column:It doesn’t reset the Identity column.Resets the Identity column.Resets the Identity column.
Permission:It requires the DELETE permission on the table.Requires ALTER permission on the schema to which the table belongs and CONTROL permission on the table.Requires ALTER permission on the table.
Memory management:The delete command does not free up the memory space allocated to the table.The Drop command releases the memory space allocated to the table.The truncate command does not release the memory space allocated to the table.
Difference between delete, drop and truncate in SQL

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

FAQ

Q: What is the difference between DELETE, TRUNCATE and DROP commands in SQL?

The DELETE command is used to remove specific rows from a table based on a WHERE condition. The TRUNCATE command is used to remove all data from a table, but unlike DELETE, it is much faster as it does not maintain a log of deleted rows. The DROP command is used to completely remove a table from the database, along with all its rows, indexes, and privileges.

Q: Can you Rollback the DELETE command?

Yes, DELETE operations can be rolled back, meaning that the deleted records can be restored.

Q: Can you Rollback the TRUNCATE command?

No, TRUNCATE operations cannot be rolled back, meaning that deleted records can’t be restored.

Q: Can you Rollback undo the DROP command?

No, DROP operations cannot be rolled back, meaning that the table, along with all its data, indexes, and privileges, cannot be recovered once it is dropped.

Q: Is the DELETE command faster than the TRUNCATE command?

No, TRUNCATE is faster than DELETE as it does not maintain a log of deleted rows.

Q: Can you use the WHERE clause with the TRUNCATE command?

No, the TRUNCATE command removes all data from a table and it doesn’t use the WHERE clause to filter specific rows.

Q: How does DROP command differ from the TRUNCATE command?

DROP removes a table and everything in it completely from the database. TRUNCATE only removes all the data inside the table but the table itself still exists in the database.

Q: Can you use DELETE, TRUNCATE, and DROP commands on indexed views?

No, TRUNCATE and DROP commands are not allowed on indexed views but you can use the DELETE command on indexed views.

You might want to read this too:

5 1 vote
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments