SQL SERVER – Delete Duplicate Records – Count Duplicate Records Links

I have wrote following two articles for Duplicate Rows Management in SQL Server. One shows how to count duplicate records and the other how to delete duplicate records.

SQL SERVER – Count Duplicate Records – Rows

SQL SERVER – Delete Duplicate Records – Rows

What to Check Before You Delete Duplicate Records

The first question is always the same: what makes two rows duplicates? Sometimes every column matches. More often, only a few business columns match, such as a customer email or an order number, while an identity column or a timestamp is different. Write that rule down before you touch the data, because the rest of the work depends on it.

Once the rule is clear, the steps are simple:

• Count first. A GROUP BY on the columns that define a duplicate, with HAVING COUNT(*) > 1, shows you how many groups you have and how big they are.
• Decide which row to keep, for example the oldest or the newest one.
• On SQL Server 2005 and later, ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) inside a CTE numbers the rows in each group. Every row with a number greater than 1 is an extra copy, and you can delete from the CTE directly.
• Run the delete inside a transaction on a test copy first, and compare the row counts before and after.

Check for foreign keys too. If child rows point to a copy you plan to remove, move them to the row you keep first. Otherwise the delete fails, or with cascading keys it quietly takes the child rows with it.

Take a backup before you run it on production. A delete is quick to write and slow to undo, and a wrong PARTITION BY list can remove rows that were not duplicates at all.

Finally, stop the problem from coming back. Once the table is clean, add a unique constraint or a unique index on the columns that define a duplicate. Then SQL Server rejects the next duplicate at insert time, and you will not need to clean the table again. If you cannot add a constraint, find the process that inserts twice. It is often a retry or an import that runs more than once.

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