It does not require you to temporarily copy the duplicate records to another table.Method 2 is simple and effective for these reasons: If your logic to delete duplicates requires choosing which records to delete and which to keep based on the sorting order of other columns, you could use the ORDER BY expression to do this. This value indicates that the records are duplicates.īecause of the (SELECT NULL) expression, the script does not sort the partitioned data based on any condition. Deletes all records that received a DupRank value that is greater than 1.Uses the ROW_NUMBER function to partition the data based on the key_value which may be one or more columns separated by commas.The ROW_NUMBER function that was introduced in Microsoft SQL Server 2005 makes this operation much simpler: DELETE T This method also incurs overhead because you are moving the data.Īlso, if your table has an IDENTITY column, you would have to use SET IDENTITY_INSERT ON when you restore the data to the original table. However, it requires you to have sufficient space available in the database to temporarily build the duplicate table. Moves the rows in the duplicate table back into the original table.Deletes all rows from the original table that are also located in the duplicate table.Moves one instance of any duplicate row in the original table to a duplicate table.
This script takes the following actions in the given order: Run the following script: SELECT DISTINCT * Then, try the following methods to remove the duplicate rows from the table. For demonstration, start by creating a sample table and data: create table original_table (key_value int ) There are two common methods that you can use to delete duplicate records from a SQL Server table. Original product version: SQL Server Original KB number: 70956 Summary This article provides a script that you can use to remove duplicate rows from a table in Microsoft SQL Server.