For more details on row_number() function, see my other article – Ranking functions And it does 1 scan count and 62 logical reads, which is much more convincing in comparison of the old way. Now it’s time to see the statistics of this query.įirst, it is executed in one go. So the delete statement is simple and clear. So if one record is triplicated, the row number will be generated 1, 2, and 3 respectively. for one businessentityid, it generates a row number.
![adventureworks2012 businessentityid adventureworks2012 businessentityid](https://dataedo.com/asset/img/kb/query/sql-server/foreign_key_columns.png)
It virtually created one more column having Id based on the repetition of a businessentityid i.e. So how this query worked? The logic seems to be simple. Select ROW_NUMBER() over (partition by BusinessEntityId order by BusinessEntityId) RNo, * from DuplicateVendor So as a whole 180 scan counts and 540 logical counts. And for each iteration, it is doing 3 scan counts and 9 logical counts. execution plan for deleting duplicate records old wayįirst of all, since it is a loop and it is deleting one row at a time, so it is executed 60 times. It deletes if duplicate record exists and as the rowcount is set to 1, it will delete only one record. Also it is using the rowcount to ensure deletion of only one row at a time. Where BusinessEntityID in (select BusinessEntityID from DuplicateVendor
![adventureworks2012 businessentityid adventureworks2012 businessentityid](https://www.mssqltips.com/tipimages2/6818_sql-select-examples.007.png)
Let’s first try the old way of deleting duplicate records.
![adventureworks2012 businessentityid adventureworks2012 businessentityid](https://coldlogics.files.wordpress.com/2011/03/031211_2205_graphingati2.png)
So after this script, we have total of 110 records, out of which, 20 records are triplicated, 20 records are duplicated and 10 records are unique. create a table DuplicateVendor with top 50 records from Purchasing.Vendor table The script to generate the data goes here. For this, I have used Purchasing.Vendor table. (I am using AdventureWorks2012 database for query).įirst of all, we need to have a table having duplicate records. In this article, we will learn both the ways and also compare them. This is one of the most common problems, we as a database developer face, if the table doesn’t have primary key and/or other constraints defined to have every row unique.īefore sql server 2005, we had to create a program/procedure to delete the duplicate records.