-- Delete Duplicates using CTE
-- Version : SQL Server 2008

---

-- setup duplicates to remove

---

IF NOT(OBJECT_ID(N'tempdb..[#TableWithDuplicates]') IS NULL)
DROP TABLE tempdb..[#TableWithDuplicates]
GO
CREATE TABLE #TableWithDuplicates (
KeyColumn VARCHAR(100) PRIMARY KEY NOT NULL,
Column1 VARCHAR(100),
Column2 VARCHAR(100)
);
INSERT INTO #TableWithDuplicates(KeyColumn, Column1, Column2) VALUES
('A','Duplicate2','Duplicate3'),
('B','Duplicate2','Duplicate3'), -- Duplicate row
('C','Duplicate2','Duplicate3'), -- Duplicate row
('D','DuplicateY','DuplicateZ'),
('E','DuplicateY','DuplicateZ'), -- Duplicate row
('F','DuplicateY','DuplicateZ'), -- Duplicate row
('G','DuplicateY','DuplicateZ'), -- Duplicate row
('H','DuplicateY','DuplicateZ') -- Duplicate row

---

-- before

---

SELECT * FROM #TableWithDuplicates;

---

-- run test

---

with d_cte as(
SELECT
KeyColumn,
COALESCE(Column1,'') AS Column1,
COALESCE(Column2,'') AS Column2, row_number() over(partition by column1, column2 order by keycolumn, column1, column2) as row_id
FROM #TableWithDuplicates
)
delete from d_cte where row_id >1;

---

-- after

---

SELECT * FROM #TableWithDuplicates