In this situation, you should use Method 1 or some similar method instead. However, this method does not work in outdated versions of SQL Server that do not support the ROW_NUMBER function. For best performance, you should have a corresponding index on the table that uses the key_value as the index key and includes any sorting columns that you might have used in the ORDER BY expression. It does not require you to join the original table with itself (for example, by using a subquery that returns all duplicate records by using a combination of GROUP BY and HAVING).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. Create a new table with the structure the same as the original table that you want to delete duplicate rows. The following shows the steps for removing duplicate rows using an intermediate table: 1. However, it requires you to have sufficient space available in the database to temporarily build the duplicate table. B) Delete duplicate rows using an intermediate table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |