Remove Duplicate Rows

Create a table with duplicate values

CREATE TABLE testdup(Col1 INT, Col2 INT)
INSERT INTO testdup
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4

How to select unique rows

Select Col1,Col2 from testdup group by col1, col2

Select distinct * from testdup


Delete Duplicate Rows

With CTE (Col1,Col2, RowNum)
As
(
 Select Col1,Col2, Row_Number() over (partition by col1,col2 order by col1,col2) as RowNum from testdup
)
Delete From CTE where RowNum>1


Get only duplicate rows(one row for each set)

Select Col1,Col2 from testdup group by col1, col2 Having count(*)>1

If table has an Identity column than it is an easy way to delete duplicate rows

Get Duplicate Rows

Select * from testdup where autoid not in ( select min(autoid) from testdup group by col1,col2)

Get Unique Rows

Select * from testdup where autoid in ( select min(autoid) from testdup group by col1,col2)

Delete Duplicate Rows

Delete from testdup where autoid not in ( select min(autoid) from testdup group by col1,col2)

Download the entire script from here

RemoveDuplicateRows