How to remove duplicate rows from a table with using primary key or identity column

The following sql query shows how to remove duplicate values with using primary key field, identity column or any temporary table.


IF OBJECT_ID('dups') IS NOT NULL
DROP TABLE Dups

CREATE TABLE Dups(ID int)
Go

INSERT INTO Dups(ID) VALUES(1)
INSERT INTO Dups(ID) VALUES(2)
INSERT INTO Dups(ID) VALUES(2)
INSERT INTO Dups(ID) VALUES(3)
INSERT INTO Dups(ID) VALUES(4)
INSERT INTO Dups(ID) VALUES(4)
INSERT INTO Dups(ID) VALUES(4)
INSERT INTO Dups(ID) VALUES(5)
INSERT INTO Dups(ID) VALUES(5)
GO

SELECT * FROM Dups
GO

WITH CTE AS
(
select row_number() over (order by id) AS RowNumber,ID from Dups
)
DELETE FROM CTE
WHERE RowNumber IN (
SELECT C1.RowNumber
FROM CTE C1
JOIN CTE C2 ON C1.ID = C2.ID and C1.RowNumber > C2.RowNumber
)
GO

SELECT * FROM Dups
GO