To remove rows from a SQL Server table, use DELETE with a WHERE clause. Wrapping it in a transaction lets you check the result before making it permanent:
BEGIN TRANSACTION;
DELETE FROM dbo.Salesperson
WHERE LeftOn < '2025-01-01'; -- (1 row affected)
SELECT * FROM dbo.Salesperson; -- look before you commit
COMMIT TRANSACTION; -- or ROLLBACK TRANSACTION to undo
Without BEGIN TRANSACTION, SQL Server runs in autocommit mode: each statement is committed as soon as it finishes, and there is nothing to roll back. A DELETE with no WHERE clause removes every row in the table.
The examples use this table:
CREATE TABLE dbo.Salesperson (
SalespersonID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL,
Region varchar(20) NOT NULL,
Salary decimal(10,2) NOT NULL,
LeftOn date NULL -- NULL = still employed
);
INSERT INTO dbo.Salesperson (Name, Region, Salary, LeftOn) VALUES
('Asha', 'East', 52000, NULL),
('Ben', 'East', 18000, '2024-03-31'),
('Carla', 'West', 61000, NULL),
('Dev', 'West', 18000, '2025-11-30'),
('Elif', 'North', 47000, NULL);
Check what you are about to delete
Run the same WHERE clause as a SELECT first:
SELECT * FROM dbo.Salesperson WHERE Salary = 18000; -- Ben and Dev in the sample data
DELETE FROM dbo.Salesperson WHERE Salary = 18000;
After the delete, @@ROWCOUNT holds the number of rows removed. Read it in the very next statement, because almost every statement resets it. Remember that a condition on a nullable column never matches NULLs: WHERE LeftOn < '2025-01-01' leaves current staff alone because their LeftOn is NULL.
Delete rows based on another table
T-SQL allows a second FROM clause with a join. Give the target table an alias and delete from the alias:
CREATE TABLE dbo.ClosedRegion (Region varchar(20) NOT NULL PRIMARY KEY);
INSERT INTO dbo.ClosedRegion VALUES ('North');
DELETE sp
FROM dbo.Salesperson AS sp
JOIN dbo.ClosedRegion AS c ON c.Region = sp.Region;
The portable equivalent uses EXISTS:
DELETE FROM dbo.Salesperson
WHERE EXISTS (SELECT 1 FROM dbo.ClosedRegion AS c
WHERE c.Region = dbo.Salesperson.Region);
Keep a copy of what was deleted
The OUTPUT clause returns the removed rows through the deleted pseudo-table. Add INTO to archive them in the same statement, so the delete and the copy succeed or fail together:
CREATE TABLE dbo.SalespersonArchive (
SalespersonID int NOT NULL,
Name varchar(50) NOT NULL,
Region varchar(20) NOT NULL,
Salary decimal(10,2) NOT NULL,
LeftOn date NULL,
DeletedAt datetime2(0) NOT NULL DEFAULT SYSDATETIME()
);
DELETE FROM dbo.Salesperson
OUTPUT deleted.SalespersonID, deleted.Name, deleted.Region,
deleted.Salary, deleted.LeftOn
INTO dbo.SalespersonArchive (SalespersonID, Name, Region, Salary, LeftOn)
WHERE LeftOn IS NOT NULL;
Restrictions: the OUTPUT INTO target cannot have enabled triggers, check constraints, or take part in a foreign key. Without INTO, the rows go back to the client, which is not allowed when the table being deleted from has enabled triggers.
DELETE, TRUNCATE TABLE or DROP TABLE
DELETE | TRUNCATE TABLE | DROP TABLE | |
|---|---|---|---|
| Removes | Rows matching WHERE, or all rows | All rows (or whole partitions) | The table, its data, indexes, triggers and constraints |
| Table still exists | Yes | Yes | No |
| Logging | Every row | Page deallocations only | Page deallocations only |
Fires DELETE triggers | Yes | No | No |
| Identity column | Keeps counting | Reset to the seed | Not applicable |
| Referenced by a foreign key | Allowed if no child rows match | Not allowed | Not allowed until the foreign key is dropped |
| Minimum permission | DELETE on the table | ALTER on the table | ALTER on the schema or CONTROL on the table |
| Can be rolled back inside a transaction | Yes | Yes | Yes |
Use TRUNCATE TABLE dbo.Salesperson; when you want an empty table: it is much faster and writes very little to the log. It is still a logged, transactional operation; the belief that it cannot be rolled back is wrong.
TRUNCATE TABLE is refused on tables that are referenced by a foreign key from another table, that take part in an indexed view, that are published with transactional or merge replication, or that are system-versioned temporal tables. On a partitioned table you can empty selected partitions with TRUNCATE TABLE ... WITH (PARTITIONS (2, 4 TO 6)).
If you delete all rows with DELETE and want numbering to restart, reseed the identity:
DBCC CHECKIDENT ('dbo.Salesperson', RESEED, 0); -- next row gets 1
DELETE only removes rows. Views, triggers and other objects are removed with DROP VIEW, DROP TRIGGER and so on.
Foreign keys
Deleting a parent row that still has child rows fails:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Salesperson". The conflict occurred in database "SalesDB", table "dbo.Orders", column 'SalespersonID'.
Delete or reassign the child rows first, in the same transaction. Alternatively define the foreign key with ON DELETE CASCADE (or SET NULL) if that really is the rule you want; a cascade makes one small DELETE capable of removing a great deal. Index the foreign key column in the child table, or every parent delete scans the child table to check for references.
Deleting a large number of rows
One DELETE is one transaction, however many rows it touches. On millions of rows that causes three problems:
- Log growth. Every deleted row is logged in every recovery model, and none of that log can be reused until the statement finishes. If the log fills, the statement fails with error 9002 and rolls back.
- Blocking. SQL Server takes row or page locks, and once a statement holds roughly 5,000 locks on one table it tries to escalate to a table lock, which blocks other sessions.
- Slow rollback. Cancelling or killing a long-running delete undoes all of it, which can take as long as the work done so far.
Delete in batches instead, each one its own short transaction:
DECLARE @rows int = 1;
WHILE @rows > 0
BEGIN
DELETE TOP (4000)
FROM dbo.Salesperson
WHERE LeftOn < '2020-01-01';
SET @rows = @@ROWCOUNT;
END;
Keep these points in mind:
- A batch size below 5,000 stays under the lock-escalation threshold.
- The
WHEREcolumn needs an index, otherwise every batch scans the table to find its rows. - Between batches the log can be reused: after a checkpoint in the
SIMPLErecovery model, after a log backup inFULL. Keep log backups running during a big purge. TOPin aDELETEpicks arbitrary rows and does not acceptORDER BY. To delete the oldest first, go through a CTE:WITH batch AS (SELECT TOP (4000) * FROM dbo.Salesperson WHERE ... ORDER BY LeftOn) DELETE FROM batch;
If you are removing most of a table, it is usually cheaper to copy the rows you want to keep into a new table, then truncate or swap. For recurring purges by date, partition the table and truncate or switch out old partitions.
Two engine features reduce the pain on current versions. Accelerated database recovery (SQL Server 2019 and later, always on in Azure SQL Database) makes rollback fast regardless of transaction size. Optimized locking (Azure SQL Database, and SQL Server 2025 where you enable it per database) holds far fewer locks during large modifications, so lock escalation becomes much less likely.
Why the table is not smaller afterwards
Deleted rows are first marked as ghost records and cleaned up by a background task. Emptied pages in a clustered index are released, but a heap keeps its empty pages unless the delete held a table lock (DELETE FROM dbo.Salesperson WITH (TABLOCK) ...). Either way, space freed inside the database stays in the data file for reuse; the file does not shrink. Rebuild the index or heap to compact a table after a large delete.
Getting deleted rows back
There is no undelete. Your options, in order of convenience:
ROLLBACK TRANSACTION, if the delete ran in an explicit transaction that is still open.- An archive table filled by
OUTPUT ... INTO, as shown above. - The history table, if the table is a system-versioned temporal table.
- Restore a backup to a new database name, to a point in time just before the delete if the log backups allow it, and copy the rows back.
Many applications avoid the question with a soft delete: an IsDeleted or DeletedAt column that queries filter on.
Common mistakes
- Running the statement without the
WHEREclause, often by executing only the highlighted first line in the query editor. - Expecting a
COMMITto be needed, then leaving an explicit transaction open and blocking everyone else. - Deleting millions of rows in one statement on a busy system.
- Using
TRUNCATE TABLEand being surprised that the identity restarted or that an audit trigger did not fire. - Writing a trigger that assumes one row. An
AFTER DELETEtrigger fires once per statement, and itsdeletedtable holds every row removed.
Further reading
Get the weekly commit
New database deep dives every week.
