Remove Duplicate Records

Following set of codes remove duplicate records from a SQL Server table, based on one or more unique fields.

WITH NewDataSet AS
( SELECT s.*, DENSE_RANK() OVER (PARTITION BY s.productid order by newID()) as r
FROM isuradb.dbo.Products s
)
SELECT r FROM NewDataSet
DELETE FROM NewDataSet WHERE r <> 1

DENSE_RANK() function returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.