The DELETE and TRUNCATE statements are both meant to delete content from a table. Since that’s true, it begs the question: What’s the difference?
In this very brief tutorial, we’ll walk through the main differences between DELETE and TRUNCATE and explain when you should choose to use one over the other.
Difference # 1: DELETE supports a WHERE clause while TRUNCATE does not
If we wanted to delete specific rows from a table (versus all rows from a table), we could add a WHERE clause to our DELETE statements. Here’s an example:
DELETE FROM Products WHERE ProductID = 50
This would of course only delete products with an ID of 50. If you wanted to, you could leave off the WHERE clause to delete all content from the table.
Understand that the TRUNCATE TABLE does not support a WHERE clause at all. The TRUNCATE TABLE statement is meant to delete absolutely everything from a table. Here is what a TRUNCATE TABLE statement looks like:
TRUNCATE TABLE Products
Again, this statement would delete everything from the Products table. That is the purpose and goal of the TRUNCATE statement.
Difference # 2: DELETE operations are logged, while TRUNCATE operations are not
When we run a DELETE statement, SQL Server logs the operation. An entry is written to the database .LDF file behind the scenes. Since this history exists, a database administrator could use it to retrieve the data in the event that it was deleted accidentally.
TRUNCATE statements, on the other hand, are not logged. Once the data is gone, it’s gone.
A database administrator would need to restore a full database backup to get the data back, which would be much more work. This is of course assuming a recent database backup exists. If your database administrator is worth anything at all, a backup will indeed exist.
Difference # 3: DELETE can be slower than TRUNCATE
Because TRUNCATE statements are not logged, they end up being faster than DELETE statements.
If you intend to delete absolutely everything from a table, I suppose you have two options:
- Run a DELETE statement without a WHERE clause
- Run a TRUNCATE TABLE statement
If you want the operation to happen quickly, and you really don’t care about the possibility of getting the data back, you should choose to run a TRUNCATE TABLE statement. Keep it quick and simple!
Next Steps:
Leave a comment if you found this tutorial helpful!
To learn more about the TRUNCATE TABLE statement, check out the following beginner-friendly tutorial:
SQL Server TRUNCATE TABLE: Everything you need to know
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
