SQL Server Remove Duplicates

Problem: Remove duplicate records from a database table.

Solution: We can achieve this using Common Table Expression and ROW_NUMBER function.

Concept:

For example we have to delete duplicate records from Color Table and need to delete Highlighted duplicate records.

ID	Color Name
1	Black
2	Blue
3	Red
4	Black
5	Red

Now arrange the table records according to color name as shown in below table.

RowNumber	ID	Color Name
1	1	Black
2	4	Black
1	2	Blue
1	3	Red
2	5	Red

Now we have to delete records with Row Number Greater than 1 Highlighted.

Query:

CREATE TABLE #ColorTable
(
    ID INT IDENTITY(1,1),
    ColorName VARCHAR(10)
)
 
INSERT INTO #ColorTable (ColorName) VALUES('BLACK');
INSERT INTO #ColorTable (ColorName) VALUES('BLACK');
INSERT INTO #ColorTable (ColorName) VALUES('BLACK');
INSERT INTO #ColorTable (ColorName) VALUES('BLACK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Blue');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
 
WITH CommonTableExpression
as
(
SELECT ROW_NUMBER() over(PARTITION BY ColorName ORDER BY ID,ColorName) RowNumber,ID,ColorName  FROM #ColorTable
)
DELETE FROM CommonTableExpression
WHERE RowNumber <> 1
 
SELECT * FROM #ColorTable

Your comments, queries and suggestions are welcomed.