In MS SQL 2005 and later editions, a much better way has been
provided to remove duplicate records smartly, while having full
control of which records to remove. It can be accomplished by using
the ROW_NUMBER() which will return a number of row within a
partition of a result set. With this code:
ROW_NUMBER() OVER (PARTITION BY Clause ORDER BY Clause)
You can have a partition on something like Email or SSN or a
combination of several fields like: First, Last, Street Number,
Street Name and City.
The idea is to perform data cleansing while removing records
where you can control any aspect of it. What's even better is the
ability to combine different pieces of data from different records
into one record while the rest of the records for that partition
(group) are removed with no data being lost. Duplicate data is a
problem that exists in many databases and the purpose of removing
duplicate records is to improve productivity and increase the
performance of your database. The below article will discuss the
technique of how to remove duplicate records - rows in MSSQL
database.
Let's create some sample data:
USE TempDB
-- Check if the table exists and if not then create it.
IF NOT EXISTS(SELECT *
FROM Sys.Tables
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[#TableWithDups]'))
-- Create temp table
BEGIN
CREATE TABLE #TableWithDups(
[PK] TINYINT NOT NULL PRIMARY KEY,
[EMail] [varchar](40) NULL,
[First] [varchar](15) NULL,
[Last] [varchar](20) NULL,
[Gender] [varchar] (1) NULL,
[DOB] [varchar](8) NULL,
[Phone] [varchar](10) NULL,
[StreetNumber] [varchar](30) NULL,
[StreetName] [varchar](30) NULL
)
END
-----------------------------------------------------------------
-- Check if some temp tables exist in the TempDB database -------
-----------------------------------------------------------------
ELSE
BEGIN
TRUNCATE TABLE #TableWithDups
END
IF EXISTS(SELECT *
FROM Sys.Tables
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[#DeDup_Testing]'))
BEGIN
DROP TABLE #DeDup_Testing
END
--------------------------------------------------------------------
--- Insert duplicates records into TableWithDups
--------------------------------------------------------------------
INSERT INTO #TableWithDups ([PK],[EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (1,'MSSQL1@RemoveDupRecords.Com','Susan','Smith','F', '19600912','','1100','Main Street')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (2,'MSSQL1@RemoveDupRecords.Com','Susan','Smith','', '','1113058336','1100','Main Street')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (3,'MSSQL1@RemoveDupRecords.Com','Susan','Smith','F','','1113058336','1100','Main Street')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (4,'MSSQL2@RemoveDupRecords.Com','Joe','Smith','','19640825','2223058336','1100','Main Street')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (5,'MSSQL2@RemoveDupRecords.Com','Joe','Smith','M','19640825','2223058336','1100','Main Street')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (6,'MSSQL3@RemoveDupRecords.Com','Alex','Walker','M','','5553058888','55','KingFisher Road')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (7,'MSSQL3@RemoveDupRecords.Com','Alex','Walker','F','19580912','','55','KingFisher Road')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (8,'MSSQL3@RemoveDupRecords.Com','Alex','Walker','','1952','','55','KingFisher Road')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (9,'MSSQL4@RemoveDupRecords.Com','Uri','Pearl','M','1961','3333054422','110','Robertsville Road')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (10,'MSSQL4@RemoveDupRecords.Com','Uri','Pearl','','19600912','3333054422','110','Robertsville Road')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (11,'MSSQL4@RemoveDupRecords.Com','Uri','Pearl','','','','110','Robertsville Road')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (12,'MSSQL5@RemoveDupRecords.Com','Terry', 'Thurber','M','','4443058377','500','Larkin Street')
INSERT INTO #TableWithDups ([PK], [EMail],[First],[Last],[Gender],[DOB],[Phone],[StreetNumber],[StreetName])
VALUES (13,'MSSQL5@RemoveDupRecords.Com','Terry', 'Thurber','','19600809','','500','Larkin Street')
Below is some sample code to explain this idea of how to remove
duplicates using the MS SQL ROW_NUMBER and partition idea:
SELECT
[First],
[Last],
[Email],
[DOB],
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Email,DOB DESC,Gender) AS Rn,
StreetNumber,
StreetName
FROM
#TableWithDups
In the above, the Email works as a partition and has a unique
value to allow us to create a group of records on that field and
also control the order of how the records will show up. In the data
below Susan Smith with the record that has the value of Rn = 2 and
Rn=3 are considered as duplicate records and should be deleted.
In actuality the only difference compared to the original data
that was created in the temp table named: TableWithDups is the
additional column named: Rn. Every record in the Rn that is greater
than 1 is considered as a duplicate one. Adding the ORDER BY on
different fields can change the way the records will show up as
Rn=1. See Sample 1 below to get the idea:
Sample 1:
When running the below code as Common Table Expression (CTE)
flexibility is increased as shown below and a WHERE clause is added
as WHERE Rn = 1. As a side note using the CTE is considered as a
temporary result. The CTE can be self-referenced multiple times in
the same query. It is similar to a derived table but much more
flexible.
;
WITH DeDupe
AS
(
SELECT
[PK],
[First],
[Last],
[Email],
[Gender],
[DOB],
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Email,DOB DESC) AS Rn,
StreetNumber,
StreetName
FROM
#TableWithDups
)
SELECT [PK],[First],[Last],[Email], [Gender],[DOB],Rn,StreetNumber,StreetName
FROM DeDupe WHERE Rn = 1
ORDER BY [First],[Last]
Here is the data as shown below in Sample 2 after running the
above code:
Sample 2:
As you see in the above in Sample 2, all duplicate records that
have Rn > 1 do not show up anymore. But if you look closely we
lost some data that could have been combined in the record with
Rn=1. We can increase the partition by adding more fields to make
sure there are no duplicate addresses, but that would not change
the fact that some data will be lost.
The real power of the above technique is that we can combine
several values from different duplicate records in the record that
gets the value of Rn=1. Let's examine closely what we have above.
If you look at Sample 2 then Joe Smith and Terry Thurber lost the
data in the Gender column. Looking at Sample 1 under PK= 5, the
Gender is M for Joe and also PK = 13 which is Terry Thurber. The
same problem exists also for the Phone number as well. The
following code will take care of this problem and incorporate all
required pieces of data in one record.
;
WITH DeDupe
AS
(
SELECT
PK,
[First],
[Last],
[Email],
[Gender],
[DOB],
MAX(Phone) OVER (PARTITION BY Email) AS Phone_Updated ,
MAX(CASE WHEN CAST(DOB AS INT) > 0 THEN CAST(DOB AS INT) ELSE DOB END)
OVER (PARTITION BY Email) AS DOB_Updated,
MAX(Gender) OVER (PARTITION BY Email) AS Gender_Updated,
ROW_NUMBER() OVER (PARTITION BY Email,First,Last,LTRIM(LEFT(UPPER([StreetNumber]),4)) + LEFT(UPPER([StreetName]),4)
ORDER BY DOB DESC,Phone DESC,Gender DESC,Email,First,Last) AS Rn,
[Phone],
[StreetNumber],
[StreetName]
FROM #TableWithDups
)
SELECT
PK,
[First],
[Last],
[Email],
[Gender],
[Gender_Updated],
[DOB],
CASE WHEN CAST(DOB_Updated AS VARCHAR(10))='0' THEN '' ELSE CAST(DOB_Updated
AS VARCHAR(10)) END DOB_Updated,
[Phone],
[Phone_Updated],
[Rn],
[StreetNumber],
[StreetName]
INTO #DeDupe_Testing FROM DeDupe WHERE Rn > 0
-- Let's show some data
SELECT * FROM #DeDupe_Testing WHERE Rn=1
The below data is the result of running the above code:
Sample 3:
The below is the continuation of the above data.
Using the MAX(Phone) with the partition technique on each field
that we don't want to lose data, will make sure to get the data in
the row that Rn=1. Having this code on the Phone MAX(Phone) OVER
(PARTITION BY Email) AS Phone_Updated and create a new column to
show the new data near the original column helps to analyze the
data to make sure everything works fine.
In the above code we make sure that no data is lost while we
remove all duplicate records. When all partitions are handled then
we create ROW_NUMBER() OVER (PARTITION…) on all combinations of
fields that create a unique partition. We also ORDER each field
that was part of the MAX() as DESC. Order the field as DESC is
crucial and is part of the technique of merging all data from
different rows into one row of the same partition. In addition, I
added code around the DOB (Date of Birth) since in some cases it
has only 0 value and clearing the DOB to nothing is a better data
for me.
In sample 3 all fields such as: Gender_Updated, DOB_Updated and
Phone_Updated are filled with data while all other duplicate
records were removed. In Sample 3 Joe Smith and Terry Thurber did
not lose any data compared to Sample 2. In addition, the idea is to
copy all records to a different table while leaving the original
data as UN touched for a backup.
The DOB is a VARCHAR (10), since when import data it is always a
character based field that comes in many different formats. For
additional info on that matter please see this article at: data cleansing and manipulating date
format.
Conclusions:
==========
Once you have found your duplicate records, you face the problem
of merging multiple records into one without losing any data. Some
of the problems you might face when merging records could be that
it has a phone or another record could have an email address and
you cannot afford to remove these duplicate records when you are
taking the risk of losing some important data. The duplicate record
remover that was discussed above covers all of these points and
provides a solution that safely removes duplicate records from your
database. In addition, to use the code above the Sample 3 data is
when you know for sure that you may have fields with empty data;
otherwise simply use the code above the Sample 2 data.