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
GO
INSERT INTO [Customers]
    SELECT TOP 10 *
    FROM [Customers]
GO

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
GO
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):

SET NOCOUNT ON
SET ROWCOUNT 1
WHILE 1 = 1
 BEGIN
   DELETE  
   FROM Customers
   WHERE contactname IN
        (SELECT  contactname
         FROM    Customers
         GROUP BY contactname
         HAVING  COUNT(*) > 1)
      IF @@Rowcount = 0
      BREAK ;
 END
 SET ROWCOUNT 0

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

Conclusion

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.