If you're working with databases, one common problem you might face is duplicate data. Duplicates can cause a lot of headaches, especially when it comes to data analysis or reporting. But don’t worry! In this post, we’ll walk through a simple way to remove duplicate records in SQL Server using a stored procedure that you can use in your own projects.
We’ll break down the SQL code step-by-step to make sure you understand how it works. By the end of this tutorial, you’ll be able to use this technique to keep your data clean and organized.
Step 1: What is a Duplicate Record?
A duplicate record is when you have two or more rows in your table with the exact same data in one or more columns. For example, if you have a list of customers and two customers have the same name, email, and phone number, those would be considered duplicates.
Step 2: The Concept of the RemoveDuplicate Procedure
In SQL Server, you can use a stored procedure to automate the process of removing duplicates. A stored procedure is like a saved set of SQL commands that can be executed later. The RemoveDuplicate procedure that we’ll look at helps to:
- Find duplicate records in a table.
- Remove the extra copies, leaving only one unique row.
Step 3: Understanding the Code
Let’s break down the SQL code that removes duplicates. Don’t worry if it seems complex at first. We’ll explain it in a way that’s easy to follow.
Step 3.1: The Procedure and Parameters
The procedure is created with the following parameters:
CREATE PROCEDURE RemoveDuplicate @SCDID sysname, @IsDebug bit = 0
- @SCDID: This is an ID that identifies the specific rule you want to apply for removing duplicates. It helps the procedure know which table to work with.
- @IsDebug: This is a flag that lets you print out the SQL commands for debugging. If you're just starting out, you can use this to see the queries that will be executed.
Step 3.2: Using ROW_NUMBER() to Identify Duplicates
The first part of the code uses a Common Table Expression (CTE) and the ROW_NUMBER() function to label rows:
WITH ct AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ColumnSearch ORDER BY ColumnSearch)
FROM SourceDatabase.SourceSchema.SourceObject
)
The ROW_NUMBER() function assigns a unique number to each row, starting from 1. The key part is PARTITION BY, which groups rows based on a specific column. For example, if you're looking for duplicates in the "Email" column, all rows with the same email will be grouped together.
After the row numbers are assigned, we can delete the duplicates.
Step 3.3: Removing Duplicates
Now that the rows are numbered, we can delete the duplicates by selecting rows with rn >= 2 (anything that’s not the first occurrence):
DELETE FROM ct WHERE rn >= 2;
This means that if there are multiple rows with the same data in the ColumnSearch, all except the first one will be deleted.
Step 3.4: Handling Column Exclusions
Sometimes, you don’t want to compare all columns for duplicates. In that case, we use the ColumnExclude parameter to exclude certain columns from the comparison. The code dynamically builds a list of column names, excluding any columns specified in ColumnExclude:
SELECT @rOut = STRING_AGG(c.name, ',')
FROM SourceDatabase.sys.COLUMNS c
WHERE c.name NOT IN (@ColumnExclude)
Step 3.5: Final Cleanup Using LAG()
In the final step, we use the LAG() function, which allows us to compare each row with the previous row in a group. If the current row has the same data as the previous one, it’s considered a duplicate and will be removed:
WITH ct AS (
SELECT *, Ckp = CONCAT(@rval),
PRv = LAG(CONCAT(@rval)) OVER(PARTITION BY ColumnSearch ORDER BY ColumnSearch)
FROM SourceDatabase.SourceSchema.SourceObject
)
DELETE FROM ct WHERE ISNULL(ckp, '') = ISNULL(PRv, '');
Step 4: Executing the Procedure
Once you understand how the procedure works, you can execute it to remove duplicates. Simply call the procedure with the SCDID (rule ID) and @IsDebug parameter (set to 1 for debugging):
EXEC RemoveDuplicate @SCDID = 'yourRuleID', @IsDebug = 1;
If @IsDebug is set to 1, the procedure will print out the SQL commands it is going to execute, so you can check them before they run.
Step 5: Original Table Design for SCD2_Rule
Here’s the original design of the SCD2_Rule table used in the stored procedure. This table contains information about your source and target tables and the columns used for comparison:
| SCDID |
SourceDatabase |
SourceSchema |
SourceObject |
TargetDatabase |
TargetSchema |
TargetObject |
ColumnSearch |
ColumnExclude |
| sysname |
sysname |
sysname |
sysname |
sysname |
sysname |
sysname |
nvarchar(-1) |
nvarchar(-1) |
Step 6: Complete SQL Stored Procedure Code
Here’s the full SQL code for the RemoveDuplicate procedure, including everything we’ve discussed so far:
CREATE PROCEDURE RemoveDuplicate
@SCDID sysname,
@IsDebug bit = 0
AS
BEGIN
DECLARE @Sql AS NVARCHAR(MAX), @rVal varchar(max)
-- Fetch the SQL query template for the given SCDID
SELECT @Sql = 'WITH ct AS (
SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ' + ColumnSearch + ' ORDER BY ' + ColumnSearch + ')
FROM ' + CONCAT(SourceDatabase, '.', SourceSchema, '.', SourceObject) + '
) DELETE FROM ct WHERE rn >= 2'
FROM dbo.SCD2_Rule WHERE SCDID = @SCDID
-- Debugging: Print the dynamic SQL if IsDebug is set to 1
IF @IsDebug = 1
PRINT @Sql
-- Execute the dynamic SQL to remove duplicates
EXEC SP_EXECUTESQL @Sql
-- Other duplicate handling: Exclude specified columns
SELECT @Sql = 'SELECT @rOut = STRING_AGG(c.name, '','')
FROM ' + SourceDatabase + '.sys.COLUMNS c
JOIN ' + SourceDatabase + '.sys.tables t ON c.object_id = t.object_id
WHERE t.name = ''' + SourceObject + '''
AND c.name NOT IN (' + b.a + ') AND c.name <> ''DataValidTo'''
FROM dbo.SCD2_Rule a
OUTER APPLY (
SELECT STRING_AGG(''' + value + ''', ',') a
FROM STRING_SPLIT(a.ColumnSearch + ',' + a.ColumnExclude, ',') b1
) b
WHERE SCDID = @SCDID
-- Debugging: Print the dynamic SQL for column exclusions
IF @IsDebug = 1
PRINT CONCAT('@Sql : ', @Sql)
-- Execute the query for excluding columns
EXEC sp_executesql @Sql, N'@rOut varchar(max) OUTPUT', @rOut=@rVal OUTPUT;
-- Debugging: Print the result of excluded columns
IF @IsDebug = 1
PRINT CONCAT('@rVal : ', @rVal)
-- Final cleanup: Remove records with identical values in key columns
SELECT @Sql = 'WITH ct AS (
SELECT *, Ckp = CONCAT(' + @rVal + '),
PRv = LAG(CONCAT(' + @rVal + ')) OVER(PARTITION BY ' + REPLACE(ColumnSearch, ',DataSystemDate', '') + ' ORDER BY ' + ColumnSearch + ')
FROM ' + CONCAT(SourceDatabase, '.', SourceSchema, '.', SourceObject) + '
) DELETE FROM ct WHERE ISNULL(ckp, '''') = ISNULL(PRv, '''')'
FROM dbo.SCD2_Rule WHERE SCDID = @SCDID
-- Debugging: Print the final SQL for cleanup
IF @IsDebug = 1
PRINT @Sql
-- Execute the final cleanup query
EXEC SP_EXECUTESQL @Sql
END
Step 7: Conclusion
You’ve just learned how to remove duplicate records from your SQL Server tables using a stored procedure. The RemoveDuplicate procedure is powerful because it allows you to automate the process of cleaning up your data.
Key Takeaways:
ROW_NUMBER() is used to assign a unique number to each row based on certain columns.LAG() compares rows to detect duplicates.- Dynamic SQL is used to make the procedure flexible for different tables and columns.
- Debugging is made easy with the
@IsDebug flag to see the SQL commands before they run.
Now you can keep your SQL Server tables clean by removing duplicate data with ease. If you have any questions or need further clarification, feel free to ask in the comments!
Bonus Tip:
If you're working with large datasets, be sure to test the procedure on a smaller subset of your data before running it on your production database. Always back up your data first!
If you found this tutorial helpful, be sure to share it with your fellow SQL enthusiasts or subscribe for more beginner-friendly guides!
