How to Find and Remove Duplicate Rows in SQL Server

Duplicate rows creep in through double-clicked buttons, retried imports and missing constraints. Removing them in SQL Server is straightforward once you have a repeatable method: define what a duplicate is, choose which copy to keep, delete the rest safely, and then make sure it cannot happen again.

How to remove duplicate rows in SQL Server

Step 1: define what a duplicate is

Rows rarely match in every column, because each usually has its own ID and timestamp. Decide which columns make two rows "the same". For an employee import, that might be EmployeeCode alone; for payroll lines, perhaps PayrollRunId, EmployeeId and PayCode.

Step 2: count the duplicates

SELECT PayrollRunId, EmployeeId, PayCode, COUNT(*) AS copies
FROM dbo.PayrollLine
GROUP BY PayrollRunId, EmployeeId, PayCode
HAVING COUNT(*) > 1
ORDER BY copies DESC;

This tells you how big the problem is before you change anything.

Step 3: see each duplicate with ROW_NUMBER

ROW_NUMBER numbers the copies within each group. The ORDER BY decides which one counts as number 1, the one you keep:

WITH Numbered AS (
    SELECT
        PayrollLineId, PayrollRunId, EmployeeId, PayCode, Amount, CreatedAt,
        ROW_NUMBER() OVER (
            PARTITION BY PayrollRunId, EmployeeId, PayCode
            ORDER BY CreatedAt ASC, PayrollLineId ASC   -- keep the oldest
        ) AS rn
    FROM dbo.PayrollLine
)
SELECT *
FROM Numbered
WHERE rn > 1;    -- the rows that would be deleted

Change the ORDER BY to keep the newest (CreatedAt DESC) or the most complete row instead. Review this list before deleting anything, especially where the "duplicates" have different amounts.

Step 4: back up the rows you will delete

WITH Numbered AS ( /* same CTE as above */
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY PayrollRunId, EmployeeId, PayCode
        ORDER BY CreatedAt, PayrollLineId) AS rn
    FROM dbo.PayrollLine
)
SELECT *
INTO dbo.PayrollLine_Duplicates_Backup
FROM Numbered
WHERE rn > 1;

If anyone questions the cleanup later, you can show exactly what was removed or put it back.

Step 5: delete through the CTE

In SQL Server you can delete directly from a CTE that references a single table, which deletes the matching rows in that table:

WITH Numbered AS (
    SELECT ROW_NUMBER() OVER (
        PARTITION BY PayrollRunId, EmployeeId, PayCode
        ORDER BY CreatedAt, PayrollLineId) AS rn
    FROM dbo.PayrollLine
)
DELETE FROM Numbered
WHERE rn > 1;

Run it inside a transaction first if you want to check the row count before committing.

Step 6: batch it on large tables

If there are millions of duplicates, delete in batches to avoid long locks and log growth, for example with DELETE TOP (5000) in a loop against the same CTE, until no rows are affected.

Step 7: stop duplicates coming back

Cleanup without prevention means you will be doing this again next month. Add a unique constraint or unique index on the columns that define a duplicate:

CREATE UNIQUE INDEX UX_PayrollLine_Run_Employee_PayCode
ON dbo.PayrollLine (PayrollRunId, EmployeeId, PayCode);

Now any attempt to insert a duplicate fails with an error, and the application can handle it, for example by updating the existing row instead. Making imports and API calls idempotent (safe to retry) is the long-term fix.

Exact duplicates with no ID column

If a table has no key at all and rows match in every column, the same ROW_NUMBER approach still works: partition by all the columns and order by any of them. Afterwards, add a primary key.

Quick checklist

  • Agree which columns define a duplicate.
  • Count, then list the rows that will be removed.
  • Choose which copy to keep with ORDER BY.
  • Back up, then delete through a CTE (in batches if large).
  • Add a unique index so it cannot happen again.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more