Find and Delete Duplicate Records in SQL Server Safely

Duplicate records are usually the trace of a mistake: an integration that ran twice, a table without a unique constraint, data merged from several sources. The result is inflated reports, emails sent twice and wrong totals. Deleting duplicates can be done in a single query, but keeping the right row, deleting without blocking on large tables and preventing it from happening again need a bit more care.

This guide covers finding duplicates, deleting them safely while choosing which row to keep, and preventing them from coming back.

Define “Duplicate” First

Agree with the business on which columns must match for two rows to count as duplicates. Usually it’s not every column but the business key (for example a tax number, or an order number and line number). A delete run before the definition is clear can take real records with it.

Finding Duplicates

-- Customers with the same tax number
SELECT TaxNo, COUNT(*) AS cnt
FROM dbo.Customer
GROUP BY TaxNo
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

-- See every duplicate row
SELECT c.*
FROM dbo.Customer AS c
JOIN (SELECT TaxNo FROM dbo.Customer GROUP BY TaxNo HAVING COUNT(*) > 1) AS d
     ON d.TaxNo = c.TaxNo
ORDER BY c.TaxNo, c.CreatedAt;

GROUP BY treats NULLs as a single group; if business key columns can be NULL, consider that separately.

Which Row to Keep?

ROW_NUMBER() numbers the rows in each duplicate group. With ORDER BY, you decide which row gets number 1 (the one to keep): the oldest record, the most recently updated or the most complete one.

-- Check first: see the rows that will be deleted (the oldest record in each group stays)
WITH ranked AS (
    SELECT CustomerId, TaxNo, CreatedAt,
           ROW_NUMBER() OVER (PARTITION BY TaxNo
                              ORDER BY CreatedAt ASC, CustomerId ASC) AS rn
    FROM dbo.Customer
    WHERE TaxNo IS NOT NULL
)
SELECT * FROM ranked WHERE rn > 1;

Including a unique column (for example CustomerId) in the ORDER BY makes it deterministic which of two rows with the same date is kept; otherwise the result can change from run to run.

Moving Child Records to the Kept Row

-- Point orders of duplicate customers to the customer being kept
WITH ranked AS (
    SELECT CustomerId,
           FIRST_VALUE(CustomerId) OVER (PARTITION BY TaxNo
                                         ORDER BY CreatedAt ASC, CustomerId ASC) AS kept_id,
           ROW_NUMBER() OVER (PARTITION BY TaxNo
                              ORDER BY CreatedAt ASC, CustomerId ASC) AS rn
    FROM dbo.Customer
    WHERE TaxNo IS NOT NULL
)
UPDATE o
SET o.CustomerId = r.kept_id
FROM dbo.Orders AS o
JOIN ranked AS r ON r.CustomerId = o.CustomerId
WHERE r.rn > 1;

Repeat this for every table that points to the customer. To see which tables depend on a table, see our foreign key relationships guide.

Deleting the Duplicates

Once no child records point to the duplicates any more, delete them with the same ranking:

WITH ranked AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY TaxNo
                              ORDER BY CreatedAt ASC, CustomerId ASC) AS rn
    FROM dbo.Customer
    WHERE TaxNo IS NOT NULL
)
DELETE FROM ranked WHERE rn > 1;

Deleting in Batches on Large Tables

Deleting millions of duplicates in one DELETE bloats the log, holds locks for a long time and leaves Always On secondaries behind. Split the delete into small batches:

DECLARE @deleted int = 1;
WHILE @deleted > 0
BEGIN
    WITH ranked AS (
        SELECT ROW_NUMBER() OVER (PARTITION BY TaxNo
                                  ORDER BY CreatedAt ASC, CustomerId ASC) AS rn
        FROM dbo.Customer
        WHERE TaxNo IS NOT NULL
    )
    DELETE TOP (5000) FROM ranked WHERE rn > 1;

    SET @deleted = @@ROWCOUNT;
END

Each batch is its own transaction; locks are released between batches, and log backups make log space reusable. An index on the business key column keeps each round from scanning the whole table.

Preventing It from Happening Again

Cleanup is only temporary if the source isn’t closed:

-- Enforce uniqueness of the business key in the database
CREATE UNIQUE NONCLUSTERED INDEX UX_Customer_TaxNo
ON dbo.Customer (TaxNo)
WHERE TaxNo IS NOT NULL;   -- a filtered unique index if NULLs must be allowed

Checklist

• The definition of a duplicate (the business key) is agreed with the business.
• The rule for the kept row is set, and the ORDER BY ends with a unique column.
• Rows with a NULL business key are excluded from the ranking.
• Rows to be deleted were checked with a SELECT first and backed up.
• Child records were moved to the kept row.
• On large tables, the delete ran in batches.
• A unique index prevents recurrence, and the source producing duplicates is fixed.