How to DELETE Rows in SQL Server

How do I safely delete rows in SQL Server?

DELETE removes one or many rows from a SQL Server table based on an optional filter.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does DELETE do in SQL Server?

DELETE removes selected rows from a table and writes each change to the transaction log, allowing precise rollbacks and auditing.

How is DELETE different from TRUNCATE?

DELETE supports WHERE filters, fires triggers, and keeps identity values; TRUNCATE wipes the whole table, skips row-by-row logging, and resets identities.

How do I delete one customer by ID?

Filter by the primary key to remove a single record. Verify with a SELECT before committing.

DELETE FROM Customers WHERE id = 42;

How do I delete many orders before a date?

Combine DELETE with a date filter to purge old data while keeping recent orders intact.

DELETE FROM Orders WHERE order_date < '2023-01-01';

How can I delete rows with a JOIN?

Alias the target table after DELETE and join to related data to match rows safely.

DELETE o FROM Orders AS o JOIN Customers AS c ON c.id = o.customer_id WHERE c.email LIKE '%@example.com';

How do I cascade deletes safely?

Using ON DELETE CASCADE constraints

Add a foreign-key constraint with ON DELETE CASCADE so child OrderItems vanish automatically when an Orders row is deleted.

Best practices for DELETE statements

Wrap deletes in a transaction, run the same filter with SELECT first, back up data, delete in manageable batches, and index filter columns.

Common mistakes and fixes

Missing WHERE deletes everything—always include a filter. Forgetting to COMMIT leaves locks—issue COMMIT or ROLLBACK. Deleting without supporting indexes causes slow scans—create indexes on search columns.

Why How to DELETE Rows in SQL Server is important

How to DELETE Rows in SQL Server Example Usage


--Remove all order items for cancelled orders
DELETE oi
FROM OrderItems AS oi
JOIN Orders AS o ON o.id = oi.order_id
WHERE o.status = 'cancelled';

How to DELETE Rows in SQL Server Syntax


DELETE [ TOP (expression) ]
    FROM <table_name> [ WITH ( table_hint [ , ... ] ) ]
    [ OUTPUT deleted.* | <output_list> ]
    WHERE <search_condition>
    [ OPTION ( query_hint [ , ... ] ) ];

--Delete discontinued products costing more than $500
DELETE TOP (100) FROM Products
WHERE stock = 0 AND price > 500;

Common Mistakes

Omitting the WHERE clause deletes every row. Always run a SELECT with the same filter first, then wrap the DELETE in a transaction so you can ROLLBACK if needed.
Deleting huge tables in one statement causes long locks and log growth. Fix by deleting in batches using a loop with TOP (n) or setting ROWCOUNT.

Frequently Asked Questions (FAQs)

Can I undo a DELETE?

Yes. If you wrapped the DELETE in a transaction, issue ROLLBACK before committing. After COMMIT, you’ll need backups or point-in-time recovery.

How do I delete duplicate rows?

Use a CTE with ROW_NUMBER() to keep the first occurrence and delete rows where the row number > 1.

Is DELETE slower than TRUNCATE?

Usually. DELETE logs every row, fires triggers, and maintains constraints, so it’s slower than TRUNCATE, which is minimally logged and ignores most constraints.