In SQL Server there are a couple a ways you can delete rows from a table. You can use the TRUNCATE and DELETE command. Though the end result of both commands is the same, there are very important differences you should know about.
Table of contents
Whats the Difference between Truncate and Delete?
The TRUNCATE command is like a DELETE command without the WHERE clause with much less of a safety net.
When to use TRUNCATE
TRUNCATE logs less information than DELETE. This means the TRUNCATE statement executes very fast; however, it does so at the expense of not logging each row deleted. This means, that you need to be very careful when using the command (actually be careful with DELETE as well!).
Though you are able to rollback a TRUNCATE command in SQL Server, you can not do the same in Oracle.
The TRUNCATE command is simple yet extremely dangerous. Here is an example to remove all rows from the employee table:
TRUNCATE TABLE employee
If you mistakenly execute a TRUNCATE statement, it is much more difficult to recover, and you may loose data in the process. The TRUNCATE command does log the pages it removes, so it is possible to recover the pages using some advanced code.
Here are some reasons to use TRUNCATE versus DELETE:
- You want to “reset” a table to its empty state. The DMSS removes all rows and resets identity key values to their initial state.
- You need to have a super quick way of clearing out table data. I can see this occurring when you need to repeatedly import test data or you have routines that use work tables or scratch tables to store information.
- You want to remove rows from a table without activating the table’s after delete trigger.
Keep in mind that TRUNCATE locks the table, so obviously don’t use this command on a table being shared by many concurrent users.
When to use the DELETE command
The DELETE command is used to remove records from a database. It is the most common way to do so. In its simplest form you can remove all the rows from a database or you can add a WHERE clause to remove only those meeting the criteria.
When execute the DELETE command,the DBMS logs all removed rows. This means it is easier to recover from a mistake, than it would a mistaken TRUNCATE.
The command
DELETE FROM employee
Will remove all employees from the employee table; whereas,
DELETE FROM employee WHERE firstName = ‘Kris’
deletes all employees whose first name is Kris.
I would pretty much recommend using a DELETE statement in all cases, except for those special circumstances that merit a TRUNCATE.
Read More: SQL DELETE Statement >>
Here are some things that happen during a DELETE that don’t during the TRUNCATE:
- Any deletion triggers are executed on the affected table.
- You are allowed to DELETE records that have foreign key constraints defined. A TRUNCATE cannot be executed if these same constraints are in place.
- Record deletions don’t reset identity keys. This is important when you need to guarantee each row uses a key that has never been used before. Perhaps, this need to happen for audit reasons.
- Depending on the locking you are using, row locks are placed on deleted rows. Unaffected rows remain unlocked.
Conclusions about Truncate and Delete
I should point out that TRUNCATE is considered a DDL command; whereas, DELETE is DML. I think this distinction should help you further understand when to use either command and the implications for doing so.
In a nutshell use DELETE to remove one or more rows from a table. Only in special situation, such as when you need to reset a table to its initial state should you consider TRUNCATE.
What to Read Next: Data Manipulation with SQL – The Ultimate Guide >>
If I remember correctly, the TRUNCATE statement will remove all rows AND the space used by the rows (in other words, deallocates the space). DELETE doesn’t do that. That’s another interesting thing about TRUNCATE!
That’s right! TRUNCATE removes the rows and the space.
Thanks for pointing that out.
Kris.
Thanks for much for the clear explanation and providing use cases to illustrate! I’m a huge fan, and your site is at the top of my bookmarks. First place I go when I get stuck.
if i have table of 100 records then i am using delete or truncate to delete all records from table so the record is been deleted now so my question is that if i try to insert new record on same table then that new record would be inserted to record number 101 or it will start from 1 ? and i want answer for both delete and truncate.
After delete statement, new record will have an identity of 101 and if Truncate was used then the new inserted record will have an identity number 1.
If i have lot of soft deleted rows and I want to remove them as part of the maintenance. Wouldn’t truncate make more sense then delete?. What happens to the vaccum (full) after doing truncate vs delete
Truncate is all or nothing – it will delete every row in the table, so it may not be an option if you want to keep some rows.
Regarding the “vacuum” left after deleting rows, you can rebuild the table (see https://sqlmaestros.com/sql-server-alter-table-rebuild/)
a very helpful explanation about Truncate and delete. I liked a question and answering comment to.where can I get this kind helpful knowledge for Trigger?
can we use where condition in truncate statement?
Truncate removes all rows from a table. You can use a WHERE clause with DELETE. See – https://www.essentialsql.com/what-is-the-difference-between-truncate-and-delete-in-sql-server/
Good artical. very much more understandable to know about the delete and truncate .this is very help full to me.thank you.
You mentioned that rollback is possible in TRUNCATE and while answering to someone in comment section you mentioned that ROLLBACK has no effect in TRUNCATE.
Why such contradiction?
My Mistake! In SQLServer you can rollback TRUNCATE: http://codingsight.com/rollback-truncate-in-sql-server/
Each “box” on your UML table should represent a logical entity in your database. To get a start on these concepts check out
https://www.essentialsql.com/data-modeling-principles-action/
Can you please also explain their differences in terms of COMMIT and Rollback.
Since DELETE can be in a transaction, you can use COMMIT and ROLLBACK.
TRUNCATE can not be scoped in a transaction; therefore, COMMIT and ROLLBACK have no effect there.
Crisp and precise. Please keep up the good work.
Thanks! I’m glad you liked the article.
which can run under the transaction?
You can run Delete under a transaction.
Hi,
For example , in a sales table I have million rows of data from 2010 to 2016 and I wish to delete all the rows from year 2013 until 2016. How do I do this?
Hi,
You could use a statement similiar to
DELETE FROM SalesTable
WHERE Year BETWEEN 2013 and 2016
Given you have millions of rows, you may want to breakup the delete into separate statements…
See http://sqlperformance.com/2013/03/io-subsystem/chunk-deletes
After delete or truncate the table structure remains there or not.
The table structure (object) remains when you use either TRUNCATE or DELETE. If you DROP TABLE, then the object is deleted.
Hi – I’m glad you liked the post. If there are other topics you want me to cover, such as what is a deadlock? let me know. I’ll add them to my list.
Kris.
Amazing article, helped me get a grasp of both Delete and Truncate commands. Thanks a lot for your effort to put up such a great article online.