SQL SERVER – Delete Duplicate Records – Rows

Following code is useful to delete duplicate records. The table must have an identity column, which will be used to identify the duplicate records. The table in the example has ID as its identity column, and the columns holding duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Watch the video to see the above concept in action:

Look before you delete

I have lost count of the people who ran a delete like this on the wrong columns and then went very quiet. Count them first, every time. It costs you ten seconds and it shows you exactly what is about to disappear. I have written that counting query up on its own in Count Duplicate Records. If it comes back empty you have nothing to do, and if it comes back with rows, those are your duplicate groups.

What if the table has no identity column

This is the part that trips everybody up. The query above needs an identity column, and plenty of real tables do not have one. The modern answer works either way, so these days this is the one I reach for first.

WITH Duplicates AS
(
    SELECT *,
           ROW_NUMBER() OVER
           (
               PARTITION BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3
               ORDER BY (SELECT NULL)
           ) AS RowNum
    FROM MyTable
)
DELETE FROM Duplicates
WHERE RowNum > 1;

Here is what it does, in plain words. ROW_NUMBER hands out 1, 2, 3 within each group of identical rows. PARTITION BY says what counts as a group. Every row that gets a number above 1 is a copy, so those go. One row from each group survives.

Yes, you really can delete straight from a CTE. SQL Server passes the delete through to the underlying table. It surprises people the first time they see it.

Choosing which copy to keep

ORDER BY (SELECT NULL) means you do not care which row survives, which is fine when the rows are truly identical. Often they are not. If one copy is older, or one has a value the others are missing, say so in the ORDER BY.

ROW_NUMBER() OVER
(
    PARTITION BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3
    ORDER BY CreatedDate ASC
) AS RowNum

That keeps the earliest row in each group. Switch to DESC and you keep the newest. This small detail matters more than the delete itself, because once the other copies are gone you cannot change your mind.

If the table is large

On a big table, one enormous delete locks a lot and fills your log file. Delete in batches instead and let the log breathe between them.

WHILE 1 = 1
BEGIN
    WITH Duplicates AS
    (
        SELECT TOP (5000) *,
               ROW_NUMBER() OVER
               (
                   PARTITION BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3
                   ORDER BY (SELECT NULL)
               ) AS RowNum
        FROM MyTable
    )
    DELETE FROM Duplicates
    WHERE RowNum > 1;

    IF @@ROWCOUNT = 0 BREAK;
END

Take a backup before you start. Not because the script is dangerous, but because “delete the duplicates” is the kind of job where somebody realises afterwards that they were not duplicates after all.

Stopping them coming back

Cleaning duplicates is treating the symptom. If they keep appearing, the table is missing a constraint. Once the table is clean, add one.

ALTER TABLE MyTable
ADD CONSTRAINT UQ_MyTable_NoDupes
UNIQUE (DuplicateColumn1, DuplicateColumn2, DuplicateColumn3);

Now the database refuses the duplicate instead of you cleaning up after it next quarter.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.