How to delete duplicate records from your SQL Server tables – T-SQL

Below you will find two methods that can be used to remove duplicate records from you Microsoft SQL Server tables.

Method 1:

This method will only work in SQL Server 2005 and later; if you need to remove duplicates from an earlier version please upgrade you SQL Server version.  Just kidding!  Use the technique in method 2.  Actually you really should upgrade your version of SQL Server…

This method uses the Common Table Expression functionality and row_number function which was introduced in Microsoft SQL Server 2005.   The key strategy to this method is to generate a number next to the  records which denotes the row number.

So in essence we want to transform this:

Duplicated Records

Duplicated Records

To this:

DuplicatedResultWithNumber

Duplicated Results with Numbers

And remove all instances of those rows which the row number column do not correspond to 1.

Below is a script which can be used to demonstrate this.   Also you can use this link in SQL Fiddle.

Method 2:

This method should work on all versions for SQL Server.  It basically works by copying the unique rows out of the original table into a temporary table, and then deleting all records from the original table;  the unique rows from the temporary table are then copied into the original table.

Link to SQL Fiddle is here.

— Create Table

About Carlos Ferreira

Leave a Reply

%d bloggers like this: