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.
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:
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.
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:
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.