Delete Duplicates using CTE

---------------
-- setup duplicates to remove
---------------

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[TableWithDuplicates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [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');
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('B','Duplicate2','Duplicate3');
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('C','Duplicate2','Duplicate3');

INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2)
VALUES ('D','DuplicateY','DuplicateZ');
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('E','DuplicateY','DuplicateZ');
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('F','DuplicateY','DuplicateZ');
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('G','DuplicateY','DuplicateZ');
INSERT INTO TableWithDuplicates(KeyColumn, Column1, Column2) -- Duplicate row
VALUES ('H','DuplicateY','DuplicateZ');

---------------
-- before
---------------
SELECT * FROM TableWithDuplicates;

---------------
-- run test
---------------
DECLARE @KeyColumn VARCHAR(100)
, @Column1 VARCHAR(100)
, @Column2 VARCHAR(100)
, @PreviousColumn1 VARCHAR(100)
, @PreviousColumn2 VARCHAR(100) ;

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