LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 2318 min read

DELETE Statement

Learn how to remove rows from a SQL Server table using DELETE and WHERE, and when to use TRUNCATE TABLE as a faster alternative.

Introduction

Just as data needs to be updated, it also sometimes needs to be removed entirely — a cancelled order, an expired account, test data left over from development. SQL Server provides two tools for removing rows: the DELETE statement, and the faster but blunter TRUNCATE TABLE. This lesson explains both, and how to use them safely.

What You Will Learn
  • The basic syntax of the DELETE statement.
  • How to delete specific rows using WHERE.
  • What happens when DELETE is run without WHERE.
  • How TRUNCATE TABLE differs from DELETE, and when to use it.

Basic DELETE Syntax

The DELETE statement removes rows from a table. Like UPDATE, it is typically used with a WHERE clause to target only specific rows.

Basic DELETE
DELETE FROM Employees
WHERE EmployeeID = 100;


SELECT EmployeeID, FirstName FROM Employees;
Result

Click Run to see what this code prints.

The row with EmployeeID 100 (Vikram, inserted earlier in the IDENTITY_INSERT example) has been removed. Notice that DELETE removes the entire row — you cannot delete only certain columns; use UPDATE with NULL if you want to clear individual column values instead.

DELETE with WHERE

WHERE works with DELETE exactly as it does with SELECT and UPDATE, letting you target rows using any valid condition, including comparisons, BETWEEN, IN, and LIKE.

Conditional DELETE
DELETE FROM Employees
WHERE Salary < 55000;


SELECT EmployeeID, FirstName, Salary FROM Employees;
Result

Click Run to see what this code prints.

Deleting All Rows

Just like UPDATE, omitting the WHERE clause from a DELETE removes every row in the table. The table itself still exists afterward — only its data is gone.

Dangerous DELETE — Do Not Run Without Checking
-- WARNING: this deletes every row in the table!
DELETE FROM Employees;
Every Row Will Be Removed

Without a WHERE clause, DELETE FROM Employees removes all rows from the table. The table structure remains, but every row of data is gone. Always confirm your WHERE clause before executing a DELETE.

TRUNCATE TABLE

When you need to remove every row from a table quickly, TRUNCATE TABLE is a faster alternative to an unqualified DELETE. Instead of logging each row deletion individually, TRUNCATE deallocates the data pages directly, which is far more efficient on large tables. It also resets any IDENTITY column back to its seed value.

TRUNCATE TABLE
TRUNCATE TABLE Employees;


SELECT COUNT(*) AS RemainingRows FROM Employees;
Result

Click Run to see what this code prints.

DELETE vs TRUNCATE

FeatureDELETETRUNCATE TABLE
Supports WHERE clauseYesNo — always removes all rows
LoggingLogs each row deletionMinimally logged, much faster
Resets IDENTITY seedNoYes
Fires DELETE triggersYesNo
Can be used with foreign key referencesGenerally yes, if rows are not referencedNo, if the table is referenced by a foreign key
Choosing Between Them

Use DELETE when you need to remove a subset of rows, need row-level triggers to fire, or need transactional row-by-row control. Use TRUNCATE TABLE when you need to quickly and completely empty a table and don't need those features.

Common Mistakes

Avoid These Mistakes
  • Running DELETE without a WHERE clause and removing all data unintentionally.
  • Assuming TRUNCATE TABLE can be filtered with WHERE — it cannot, it always removes every row.
  • Trying to TRUNCATE a table that is referenced by a foreign key constraint, which SQL Server will block.
  • Forgetting that TRUNCATE resets the IDENTITY seed, which can cause ID reuse if not accounted for.

Best Practices

  • Always preview rows with SELECT using the same WHERE clause before running DELETE.
  • Wrap important deletes in a transaction so you can roll back if something goes wrong.
  • Use TRUNCATE TABLE only when you intend to remove every row and don't need triggers to fire.
  • Consider soft deletes (an IsDeleted flag) instead of hard deletes for data you may need to recover later.

Frequently Asked Questions

Yes, if it is executed inside an explicit transaction that has not yet been committed. Once committed, it cannot be undone without a backup.

For removing all rows from a large table, yes — TRUNCATE is significantly faster because it deallocates data pages instead of logging individual row deletions.

TRUNCATE TABLE is blocked if the table is referenced by a foreign key constraint from another table, or if it participates in replication or certain indexed views.

Key Takeaways

  • DELETE removes rows and supports a WHERE clause for targeting specific rows.
  • Omitting WHERE from DELETE removes every row in the table.
  • TRUNCATE TABLE quickly removes all rows and resets the IDENTITY seed.
  • TRUNCATE cannot be filtered and does not fire DELETE triggers.
  • Always verify what will be affected before running DELETE or TRUNCATE.

Summary

DELETE and TRUNCATE TABLE give you full control over removing data, each suited to different situations. Next, you will move from modifying data to summarizing it, starting with SQL Server's aggregate functions.

Next Lesson →

Aggregate Functions