Every row in a table is identified by a primary key. Now and then the key occurs twice in your system. That should not happen. With this simple query you can quickly find duplicate rows in your table!

The query below shows how you can find duplicate employees, based on EMPLOYEENUMBER. This should be the primary key from the source.

T-SQL
SELECT DISTINCT
EMPLOYEENUMBER
FROM    EMPLOYEES
GROUP BY EMPLOYEENUMBER
HAVING  COUNT(EMPLOYEENUMBER) > 1

Often you then do not want to include these duplicate numbers when you load your data. With the query below you can filter out duplicate employees.

T-SQL
SELECT  *
FROM    (
SELECT EmployeeNumber,
ROW_NUMBER() OVER (PARTITION BY EMPLOYEENUMBER ORDER BY EMPLOYEENUMBER ASC) AS Rij
FROM   EMPLOYEES AS LM
) AS X
WHERE   X.Rij <= 1

What we do here is add a row number per unique EmployeeNumber. If a number occurs twice, the second row gets row number 2. Then we remove all rows with a number > 1!

Frequently asked questions

How do I find duplicate rows in a SQL Server table?

Use GROUP BY on the key with HAVING COUNT(key) > 1. The example does this for EMPLOYEENUMBER in the table EMPLOYEES and shows every number that occurs more than once.

How do I remove duplicate rows when I load data?

Use ROW_NUMBER() OVER (PARTITION BY key ORDER BY key) to add a row number per unique key. A number that occurs twice gets row number 2. Then select only the rows with Rij <= 1.

What does ROW_NUMBER() with PARTITION BY do with duplicate records?

It gives a row number per unique EmployeeNumber. If a number occurs twice, the second row gets number 2. That way you can filter out the duplicates.

Why does a primary key sometimes occur twice?

A primary key should identify a row uniquely. In source systems the key still occurs twice now and then. That should not happen.

How do I keep only the first of duplicate employees?

Filter the subquery with the row number on Rij <= 1. The query keeps only the first row of each EmployeeNumber. All rows with a row number greater than 1 drop out.

Comments

Your email address is not shown. A photo comes from Gravatar, if you have one.

  1. Date calculations

    SQL Server5 Dec 2008 · 20.3k reads
    20.3k

  2. Recursive Query with CTE I

    SQL Server7 Nov 2008 · 4.7k reads
    4.7k

  3. How do I make a date dimension?

    SQL Server24 Feb 2009 · 12.4k reads
    12.4k
(3 rows affected)SQL Server