Rajendra Gupta
Insert a new record

Difference between SQL Truncate and SQL Delete statements in SQL Server

July 8, 2019 by

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.

Actual execution of a SQL Delete command

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.

Sample data

EmpID column is an identity column. We can check the identity column in a table using sp_help command.


identity property of a table

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 and identity values

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.


Output of DBCC CHECKIDENT

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


Insert a new record

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.

Delete a record from the 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.


Rollback delete 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.

Estimated execution plan of SQL Truncate

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.

Reset identity value

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.

View the records in a table.

Now, issue the Rollback Tran command and verify the records in the table. We get our data back in the table.

Rollback transaction

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
645 Views