Delete Duplicate Rows in SQL Server 2005 and 2008 using CTE

First we will create a table and insert duplicate rows from which we will later remove duplicate rows:

CREATE TABLE Duplicate_Record_Table (Value1 char, Value2 char)
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','A');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('B','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
INSERT INTO Duplicate_Record_Table (Value1, Value2)
VALUES ('A','B');
GO

We can see that there are more duplicate rows in the table:

SELECT * FROM Duplicate_Record_Table

To delete duplicate rows run following code (in this example a duplicate row in the table with two columns is when both Value1 and Value2 are the same):

WITH CTE (Value1,Value2, Duplicate_Record_Count)
AS
(
SELECT Value1,Value2,
ROW_NUMBER() OVER(PARTITION BY Value1,Value2 ORDER BY Value1) AS Duplicate_Record_Count
FROM Duplicate_Record_Table
)
DELETE
FROM CTE
WHERE Duplicate_Record_Count > 1
GO

...and 8 duplicate rows are deleted from the table:

To see how to count duplicate records or rows in SQL Server read article:

Count duplicate records or rows in SQL Server