Every DBA hits this one eventually. A table has two or more rows that are identical in every column that matters, and you need to remove all but one of them. An ETL job re-ran without a dedupe check. An import script fired twice. A missing unique constraint let bad data in through the front door.
Whatever caused it, you’re left staring at duplicate rows and need them gone, cleanly, without touching the copy you want to keep.
This post covers the safe way to find duplicates and delete them, using ROW_NUMBER() to keep exactly one row from each duplicate set.
Why It Matters
Duplicates aren’t just clutter. They break aggregates, since a SUM or COUNT over a table with duplicate rows silently doubles values that should only be counted once. They can throw off joins downstream, multiplying results in ways that are hard to spot until a report looks wrong. And if the table has no primary key, the rows are technically indistinguishable from each other, so a plain DELETE has no way to target “just one of them” without help.
The fix is a query that can tell duplicate rows apart even when the data in them can’t.
Step 1 – Find The Duplicates First
Before deleting anything, confirm what you’re dealing with. Group by the columns that define a duplicate for this table, and count how many times each combination shows up.
SELECT
CustomerID,
OrderDate,
ProductID,
COUNT(*) AS DuplicateCount
FROM dbo.Orders
GROUP BY CustomerID, OrderDate, ProductID
HAVING COUNT(*) > 1;
Any row with DuplicateCount over 1 is a set you’ll need to clean up. This step matters on its own: it tells you which columns actually define “duplicate” for this table, and gives you a row count to check your delete against afterward.
Step 2 – Delete Duplicates And Keep One Row
ROW_NUMBER() numbers every row within a group, so it can distinguish between rows that are otherwise identical. Partition by the columns that define a duplicate, order by a tiebreaker, then delete everything numbered higher than 1.
WITH DupeCTE AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY CustomerID, OrderDate, ProductID
ORDER BY OrderID
) AS RowNum
FROM dbo.Orders
)
DELETE FROM DupeCTE
WHERE RowNum > 1;
PARTITION BY defines what counts as “the same row”: every row sharing those column values gets grouped and numbered together. ORDER BY decides which copy survives, whichever row sorts first in each group gets RowNum = 1 and stays. Everything else in that group gets deleted.
Order by the column that reflects which copy you actually want to keep. OrderID ASC keeps the earliest row inserted; flip it to DESC if you’d rather keep the most recent one.
What If There’s No Primary Key?
This trips people up: if the rows are truly identical with no unique identifier anywhere, how does SQL Server know which one to keep? It doesn’t need to know, and it doesn’t need a primary key either. ROW_NUMBER() assigns sequential numbers to every row inside a partition based on the physical row set, whether or not anything in that row is unique. Two identical rows still get numbered 1 and 2. The query works exactly the same either way, and you can drop the PARTITION BY columns down to whichever subset of the table actually needs to be unique.
Verify Before You Commit
Don’t run a bulk delete against production without checking what it’s about to remove first. Swap the DELETE for a SELECT and preview the rows, or wrap the real delete in a transaction so you can review the row count before committing.
BEGIN TRAN;
WITH DupeCTE AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY CustomerID, OrderDate, ProductID
ORDER BY OrderID
) AS RowNum
FROM dbo.Orders
)
DELETE FROM DupeCTE
WHERE RowNum > 1;
-- Check @@ROWCOUNT and spot-check the table before deciding.
-- COMMIT;
-- ROLLBACK;
Compare the deleted row count against the DuplicateCount totals from Step 1. They should line up. If they don’t, stop and figure out why before you commit.
Production Notes
Large tables need batching. A single DELETE against millions of duplicate rows can blow out the transaction log and hold locks far longer than you want. Wrap the same ROW_NUMBER() logic in a loop with TOP (n), deleting a few thousand rows per batch and checking log growth in between.
Add a constraint once the table’s clean. Duplicates usually mean the schema is missing a guardrail, not just that one load script misbehaved. Once you’ve cleaned up, decide whether a unique constraint or unique index belongs on those columns so this doesn’t come back.
Watch for blocking on busy tables. A large delete takes locks that can hold up other sessions on a high-traffic OLTP table. Run it in a maintenance window if you can, and batch it either way.
Related
ROW_NUMBER (Transact-SQL) on Microsoft Docs
Finding duplicates is a GROUP BY. Removing them is a ROW_NUMBER(). Do both before you trust the table again.
Leave a Reply