How to Delete Data in SQL Server

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

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 WHERE column 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 SIMPLE recovery model, after a log backup in FULL. Keep log backups running during a big purge.
• TOP in a DELETE picks arbitrary rows and does not accept ORDER 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:

1. ROLLBACK TRANSACTION, if the delete ran in an explicit transaction that is still open.
2. An archive table filled by OUTPUT ... INTO, as shown above.
3. The history table, if the table is a system-versioned temporal table.
4. 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 WHERE clause, often by executing only the highlighted first line in the query editor.
• Expecting a COMMIT to 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 TABLE and being surprised that the identity restarted or that an audit trigger did not fire.
• Writing a trigger that assumes one row. An AFTER DELETE trigger fires once per statement, and its deleted table holds every row removed.

Further reading

• DELETE (Transact-SQL)
• TRUNCATE TABLE (Transact-SQL)
• OUTPUT clause (Transact-SQL)
• DROP TABLE (Transact-SQL)

Should I use WITH (NOLOCK) to make SQL Server queries faster?

NOLOCK doesn't just risk dirty reads. It can skip rows, count rows twice and fail with error 601. Read Committed Snapshot Isolation removes the blocking without the wrong answers.

#concurrency#locking#transactions