Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Find and Remove Duplicate Records SQL Server

By Ginger Keys,

Having duplicate records in a database is an age-old problem that almost every organization has to deal with. Duplicates can appear because of careless data input, merging records from old systems into new systems, uploading leads from purchased lists, and multiple other reasons.

Identifying these duplicate records can also be tricky. You might have multiple people with the same first and last name. You might have one person with multiple addresses, emails, or other identifying characteristics. In most cases business rules, not repetitive values, will determine what constitutes duplicate data. Knowing your data is the key to determining whether your records are duplicates or not.

It can be a painstaking process, but we will go over some basic steps to help find and remove duplicate records in your database.

Create Duplicates

First let’s take a look at a table and purposefully insert duplicate records into it. I have selected some rows out of a table that contains customer information.

Next I will insert these rows of data into my Customers table to create duplicate rows:

USE ABCompany
INSERT INTO [Customers]
    SELECT TOP 10 *
    FROM [Customers]

And the results are shown below:

Find Duplicates

Duplicate records in your table will most likely not be in sequential order, as shown in our example above. So in order to find duplicates in your table run this query, substituting the names of your database, table, and relevant columns.

USE YourDatabase
SELECT column1, column2, COUNT(column2) as Duplicates
FROM YourTable
GROUP BY column1, column2
HAVING COUNT(column2) > 1

You will need to determine which field(s) in your table will constitute duplicate records. Again, knowing your data and business rules for your organization will determine whether you have duplicate records in your database.  In our example below, the results below assume that contactname is the column we are using to determine duplicate records in the table.

Delete Duplicates

Now that we see there are indeed duplicate records in the table, we can delete duplicate rows with this script (again, you will substitute your database, table, and column names):

WHILE 1 = 1
   FROM Customers
   WHERE contactname IN
        (SELECT  contactname
         FROM    Customers
         GROUP BY contactname
         HAVING  COUNT(*) > 1)
      IF @@Rowcount = 0
      BREAK ;

To check the results, we run the select statement again to make sure the duplicates are gone:


SQL Server has methods for preventing duplicate records in a database, such as enforcing entity integrity through the use of primary key constraints, unique key constraints, and triggers. However duplicates can occasionally occur because of database design error, or repetitive data that somehow gets past these quality control methods. The techniques described above, in addition to your familiarity with your data, will help you to find and delete duplicate records in your databases.



FindDuplicates.txt | DeleteDups.txt
Total article views: 6648 | Views in the last 30 days: 15
Related Articles

Deleting Duplicate Record in Production

Deleting Duplicate Record in Production


De-duplicating Customer Detail and assigning parent child structure to multiple customer records

De-duplicating customer detail and assigning a master customer and child structure


Removing Duplicate Records

How to remove duplicate records from a table.


Deleting Duplicate Records

In Datawarehousing, a normal scenario is eleminating duplicate records/rows or deleting duplicate re...


Selecting first duplicate record

duplicate records

data integrity    
duplicate records