Difference between SQL Truncate and SQL Delete statements in SQL Server (2023)

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.

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.

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.

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.

We need to delete the EmpID 1014 from the Employee table.

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.

If we insert a new record, it uses an identity value 501.

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.

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.

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.

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.

The table is empty now. Let’s insert a new record in the table.

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.

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

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.