As a DBA working with SQL Server, some data issues arise that need to be fixed.  One such data issue is the duplication of data.  Duplicate data can happen for a number of reasons.  One such reason is the absence of constraints such as Primary Keys.

Luckily, there are ways for the Data Professional to clean up such a quagmire.  First, let’s set the stage with a really simple example.

Code block

CREATE TABLE DupeDemo (DemoID INT, SomeInt INT)
 
INSERT INTO DupeDemo
SELECT TOP 10000
        DemoID       = ABS(CHECKSUM(NEWID()))%100+1,
SomeInt      = ABS(CHECKSUM(NEWID()))%50+1
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2

Here, we are simply creating a table and populating the table with two columns of randomized data.  This should give us a significant amount of duplicate data and a good enough test bed to demonstrate how to quickly fix this issue.  Due to the random nature of these inserts, we will get a significant range of duplicates for some values and maybe no duplicates at all for other values.  (Did that make sense?)

Let’s check our work and see how well we did with the duplicate creation.

Code block

SELECT *
FROM DupeDemo
ORDER BY DemoID,SomeInt

Each time I have run this, I have come up with a good test bed.  It should work relatively well for you as well.  Now, we can work on eliminating those duplicates.  To better illustrate that we have dupes and the distribution, let’s have a look at this query.

Code block

SELECT DemoID,SomeInt, COUNT(SomeInt) AS Counted
FROM DupeDemo
GROUP BY DemoID,SomeInt
ORDER BY DemoID,SomeInt

This query will yield results like what we see in the image to the right.

Similarly, I can run a query that would be contained within a CTE and take advantage of the ROW_Number() function to help find where I may have dups.

Code block

WITH dedupe AS (
SELECT DemoID, SomeInt
,ROW_NUMBER() OVER (partition BY DemoID,SomeInt ORDER BY Someint) AS Rownum
FROM DupeDemo
)
SELECT * FROM dedupe
WHERE Rownum > 1
ORDER BY DemoID,SomeInt

Now that we know for certain that we have a large amount of dupes, lets finally work on eliminating them.  We can use a query just like the last one and alter it a bit to make it work so we can delete the dups.  Under normal circumstances, I would make absolutely certain that the tables to be affected by these deletes were backed up.  You can accomplish that by either copying the data in the table into a new table or you can run a database backup.

Code block

WITH dedupe AS (
SELECT DemoID, SomeInt
,ROW_NUMBER() OVER (partition BY DemoID,SomeInt ORDER BY Someint) AS Rownum
FROM DupeDemo
)
DELETE dedupe
WHERE Rownum > 1

Really easy script.  This will delete all source table records that are dupes.  You can validate that by rerunning either of the first two queries used to demonstrate that duplicate data existed.  This is illustrated in the following image.

Since SQL 2005, removing bad duplicate data has become substantially easier.  This example should help to demonstrate that as well as provide a method to create a repeatable test while learning the technique.