We get the requirement to remove the data from the relational SQL table. We can use both SQL Delete and SQL Truncate statement to delete the data. Understanding differences between these commands helps SQL developers to handle their data well. Additionally, this is a very common question asked in SQL beginner’s interviews.
Tell me the difference between delete and truncate command.
I have seen most of the candidates not familiar with the difference between these commands. This article gives you a complete overview of Delete and Truncate statements along with differences.
SQL Delete Command
We use SQL Delete command in SQL Server to remove records from a table. We can remove all records or use a Where clause to remove records matching the criteria.
Example 1: To remove all records from a table
Suppose we want to remove all records from an Employee table, execute the following query.
|
1 |
DELETE FROM [SQLShackDemo].[dbo].[Employee]; |
Example 2: To remove specific records from a table
Suppose we want to remove all employee records belongs to a particular city San Antonio. We can specify this condition in a Where clause with SQL Delete statement as shown below.
|
1 2 |
DELETE FROM [SQLShackDemo].[dbo].[Employee] where City='San Antonio' |
We need to use the string value within the single quotes. We can directly specify value without single quotes in the Where clause such as EmpID in the following query.
|
1 2 |
DELETE [SQLShackDemo].[dbo].[Employee] WHERE EmpID = 1001; |
In the Actual Execution Plan of a Delete clause, we can see it is using the Clustered Index Delete operator to remove a specific row.
Example 3: SQL Delete statement and identity values
Delete statement removes the records one by one and it logs each entry into the transaction log. It is a DML statement.
Suppose we remove a row from a table using the DELETE statement and that table contains the SQL IDENTITY values. IDENTITY values get generate on every new record insert. We might have a question-
What happens to an identity value once we remove a record?
Let’s look at this scenario using an example. We have the following data in the Employee table.
EmpID column is an identity column. We can check the identity column in a table using sp_help command.
|
1 |
sp_help '[Employee]' |
We need to delete the EmpID 1014 from the Employee table.
|
1 2 |
DELETE [SQLShackDemo].[dbo].[Employee] WHERE EmpID = 1014; |
Once we remove the record, view the record from the table. In the following screenshot, we can see that identity value 1014 is not available because we removed that record from the table.
SQL Delete statement does not reset the identity values in a table. We can use DBCC CHECKIDENT to check current identity value and manually set a new identity value for the identity column.
For example, in the following query, we want to reset the identity values to 500.
|
1 |
DBCC CHECKIDENT('Employee', RESEED,500) |
If we insert a new record, it uses an identity value 501.
|
1 |
insert into employee values('bb','bb','b','bb',1,555) |
Example 4: Rollback a SQL delete statement transaction
We can roll back a transaction using the delete statement. Let’s use a delete statement with BEGIN Transaction.
|
1 2 3 |
BEGIN TRANSACTION; DELETE FROM [SQLShackDemo].[dbo].[Employee] WHERE EmpID = 1010; |
We removed the record having EmpId 1010 in the employee table.
We can use to roll back the transaction. Execute this rollback command and view the records in the table. We can see the deleted record gets a rollback and we can see it in the Employee table.
|
1 |
Rollback transaction |
SQL Truncate Command
SQL Truncate is a data definition language (DDL) command. It removes all rows in a table. SQL Server stores data of a table in the pages. The truncate command deletes rows by deallocating the pages. It makes an entry for the de-allocation of pages in the transaction log. It does not log each row deletion in the transaction log.
We cannot specify any condition in the SQL Truncate command. At a high level, you can consider truncate command similar to a Delete command without a Where clause. It locks the table and the pages instead of a row.
SQL Server does not show the actual execution plan of a SQL Truncate command. In the following screenshot, you can see the estimated execution plan.
Example 5: Remove all rows of Employee table using the truncate statement
The SQL truncate command is straightforward; you just need to pass the table name to remove all rows.
|
1 |
TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee]; |
Example 6: SQL Truncate command and identity values
In the previous example 3, we explored delete command with the identity values. Delete does not reset identity values. Let’s see how the truncate command behaves with the identity values.
First, execute the command in example 5 to delete all rows of a table.
|
1 |
TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee]; |
The table is empty now. Let’s insert a new record in the table.
|
1 |
INSERT into employee values('bb','bb','b','bb',1,555) |
You can see that Identity value again starts from 1 as defined in the table properties.
Example 7: SQL Truncate command with Rollback
In example 4, w explored to roll back a transaction with the delete command. It is a misconception among DBA’s that we cannot roll back a transaction executed with the TRUNCATE command. Is it true?
Let’s explore this again with an example.
Start a transaction to truncate the employee table.
|
1 2 |
BEGIN TRAN; TRUNCATE TABLE [SQLShackDemo].[dbo].[Employee]; |
Once the command is completed, verify that there are no records in the table.
Now, issue the Rollback Tran command and verify the records in the table. We get our data back in the table.
It shows that we can roll back delete as well as truncated command started within a transaction.
Let’s explore the difference between the SQL Delete and SQL Truncate command in the following table.
Delete vs Truncate
SQL Delete | SQL Truncate |
Delete command is useful to delete all or specific rows from a table specified using a Where clause | The truncate command removes all rows of a table. We cannot use a Where clause in this. |
It is a DML command | It is a DDL command. |
SQL Delete command places lock on each row requires to delete from a table. | SQL Truncate command places a table and page lock to remove all records. |
Delete command logs entry for each deleted row in the transaction log. | The truncate command does not log entries for each deleted row in the transaction log. |
Delete command is slower than the Truncate command. | It is faster than the delete command. |
It removes rows one at a time. | It removes all rows in a table by deallocating the pages that are used to store the table data |
It retains the identity and does not reset it to the seed value. | Truncate command reset the identity to its seed value. |
It requires more transaction log space than the truncate command. | It requires less transaction log space than the truncate command. |
You require delete permission on a table to use this | You require Alter table permissions to truncate a table. |
You can use the Delete statement with the indexed views. | You cannot use the truncate command with the indexed views. |
Delete command retains the object statistics and allocated space. | Truncate deallocates all data pages of a table. Therefore, it removes all statistics and allocated space as well. |
Delete command can activate a trigger as well. Delete works on individual rows and delete the data. Therefore, it activates a trigger. | The truncate command cannot activate a trigger. The trigger is activated if any row modification takes place. In this command, SQL Server deallocates all pages, so it does not activate a trigger. |
Delete command removes the rows matched with the where clause. It also does not remove the columns, indexes, constraints, schema | The truncate command only removes all rows of a table. It does not remove the columns, indexes, constraints, and schema. |
Conclusion
In this article, we explored both the SQL Delete and SQL Truncate command to remove the rows in a table. I hope this article helps to answer all your questions related to delete and truncate commands in SQL Server and also understand the difference in these commands. You need to be careful while using the truncate command because it removes all rows in a table.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023