How to fix Error 460 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 460 – DISTINCT Not Allowed in Recursive CTE

Why This Error Happens

This error occurs because SQL Server’s query engine enforces strict ANSI SQL rules on recursive common table expressions (CTEs), and the DISTINCT keyword is explicitly disallowed within the recursive member of the CTE definition. This restriction exists because DISTINCT requires a complete result set to evaluate uniqueness, which conflicts with the iterative, row-by-row nature of recursive execution. The issue is purely a T-SQL syntax/logic violation rather than a server configuration, permissions, or corruption problem, meaning it will occur consistently regardless of the SQL Server 2025 instance or Windows environment it runs on.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Locate the exact recursive CTE referenced in the error message (the %.*ls placeholder will show the CTE name in the actual error output).
  • Open the query in SQL Server Management Studio (SSMS) or Azure Data Studio and isolate the recursive member (the part of the UNION ALL that references the CTE itself).
  • Confirm whether DISTINCT is present directly in the SELECT statement of the recursive member.

Step 2: Remove DISTINCT from the Recursive Member

  • Delete the DISTINCT keyword from the recursive part of the CTE.
  • Example of invalid syntax:
WITH cte AS (
    SELECT col1, col2 FROM Table1
    UNION ALL
    SELECT DISTINCT col1, col2 FROM Table1 t JOIN cte c ON t.id = c.id
)
  • Corrected version:
WITH cte AS (
    SELECT col1, col2 FROM Table1
    UNION ALL
    SELECT col1, col2 FROM Table1 t JOIN cte c ON t.id = c.id
)

Step 3: Move Deduplication Logic Outside the CTE

  • If duplicate rows are a genuine concern, wrap the entire recursive CTE in an outer query and apply DISTINCT there instead.
  • Example:
SELECT DISTINCT * FROM cte OPTION (MAXRECURSION 100);
  • Alternatively, use GROUP BY in the outer query if you need aggregation rather than simple deduplication.

Step 4: Validate Recursive CTE Compliance

  • Check the anchor member and recursive member for other restricted operations that may trigger similar errors (e.g., TOP, GROUP BY, HAVING, outer joins, or subqueries in the recursive part).
  • Cross-reference the query against Microsoft’s official documentation for “Guidelines for Defining and Using Recursive Common Table Expressions” to ensure full compliance.
  • Re-run the query in SSMS to confirm Error 460 no longer appears.

Alternative or Verification Steps

  • Execute the corrected query in SSMS with SET SHOWPLAN_ALL ON to verify the execution plan compiles without triggering Error 460.
  • If deduplication logic becomes overly complex when moved outside the CTE, consider refactoring the recursive logic into a stored procedure with a temporary table, applying DISTINCT or GROUP BY on the final result set after the recursion completes.