What Is DELETE and TRUNCATE in SQL?

June 27, 2025

If you’re managing data in a SQL database, sooner or later, you’ll need to remove data. Whether you’re cleaning up test entries or deleting outdated records, you’ll probably ask yourself: what is DELETE and TRUNCATE in SQL, and which one should I use?

In this post, I’ll walk you through the differences between TRUNCATE and DELETE, when to use each, and how to perform common delete operations, without making it sound like a textbook. Let’s keep it practical and real-world.

What Is the DELETE Command in SQL?

The DELETE command in SQL is used to remove specific rows from a table. Unlike TRUNCATE, which clears everything, DELETE gives you control, so you can target only the records you want gone.

Here is how you delete a single row in SQL:

DELETE FROM EmployeeSalary2025
WHERE EmployeeID = 7

This deletes a record in EmployeeSalary2025 table with EmployeeID TORTU, without touching any other record:

How to Delete Multiple Records in SQL?

Want to delete more than one record at a time? Just adjust your WHERE clause to match multiple rows:

DELETE FROM [Order Details]
WHERE ProductID = '17'

This removes all order details for ProductID 17:

You can also delete based on ranges or lists:

DELETE FROM EmployeeSalary2025
WHERE EmployeeID IN (3, 4, 8)

The above code deletes records from EmployeeSalary2025 table for employees with ID 3,4 and 8:

How to Delete Top 1000 in SQL Server?

In SQL Server, you might need to remove only the first N rows – for cleanup, testing, or performance reasons. Here’s how to delete the top 1000 rows:

DELETE TOP(1000) 
FROM [Order Details]

This deletes the first 1000 rows based on how SQL Server retrieves them:

What Is TRUNCATE in SQL?

TRUNCATE TABLE is like the “nuclear option.” It removes all rows from a table – instantly and without logging each row individually (which makes it faster).

TRUNCATE TABLE TempData

This clears the entire TempData table in one shot:

We don’t have this table in our Northwind database, so we cannot delete any records, but I hope you get the point.

What is the Difference between DELETE and TRUNCATE in SQL?

Here are the 5 key differences between truncate vs delete:

Feature	DELETE	TRUNCATE
Removes rows?	Yes – specific rows	Yes – all rows
WHERE clause?	Yes	No – removes everything
Speed	Slower (logs each row)	Faster (minimal logging)
Rollback?	Yes (if in a transaction)	Yes (in most DBs)
Identity reset?	No	Yes (resets auto-increment)

Use DELETE when you need precision. Use TRUNCATE when you want a clean slate.

Common Questions:

Can we rollback after TRUNCATE?

Yes, but only if you run the TRUNCATE statement inside a transaction and your database supports it. In SQL Server, for example, TRUNCATE is a logged operation and can be rolled back if it’s wrapped in an explicit transaction (BEGIN TRANSACTION ... ROLLBACK). If you don’t use a transaction, the data is gone and cannot be recovered.

Does TRUNCATE and DELETE free the table space?

TRUNCATE usually frees up space more effectively because it deallocates all data pages used by the table. It resets things like the identity counter and works faster because it doesn’t log individual row deletions. DELETE, on the other hand, removes rows one at a time and doesn’t release space unless you run additional commands like SHRINKDATABASE or VACUUM (depending on your database system).