How to delete duplicate rows using CTE

 Duplicate records cane be deleted in several ways in SQL Server. Delete the duplicate records using common table expression is one of method.

Let's see the below example.

Employee Table:

ID	FirstName	LastName	Gender	Salary
1	Mark	Hastings	Male	60000
1	Mark	Hastings	Male	60000
2	Mary	Lambeth	Female	30000
2	Mary	Lambeth	Female	30000
3	Ben	Hoskins	Male	70000
3	Ben	Hoskins	Male	70000
3	Ben	Hoskins	Male	70000

SQL Code:

WITH EmployeeCTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RowNumber
FROM Employee
)
DELETE FROM EmployeeCTE WHERE RowNumber > 1

Output:

ID	FirstName	LastName	Gender	Salary
1	Mark	Hastings	Male	60000
2	Mary	Lambeth	Female	30000
3	Ben	Hoskins	Male	70000

The maximum recursion has been exhausted before statement completion

OPTION (MAXRECURSION n);

While executing above statement with CTE (common table expression) or recursive CTE, error came like below

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion %d has been exhausted before statement completion.

Let's see the below example where try to control the recursion (think stack overflow in code) with MAXRECURSION as a query option that will limit the number of recursive calls.

WITH yearsAgo
(
myYear
)
AS
(
-- Base Case
SELECT DATEPART(year , GETDATE()) AS myYear
UNION ALL
-- Recursive Section
SELECT yearsAgo.myYear - 1
FROM yearsAgo
WHERE yearsAgo.myYear >= 2002
)
SELECT * FROM yearsAgo
OPTION (MAXRECURSION 10);

Output:

Msg 530, Level 16, State 1, Line 1

The statement terminated. The maximum recursion 10 has been exhausted before statement completion.