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

by

Why This Error Happens

This error occurs because SQL Server enforces strict rules on recursive Common Table Expressions (CTEs) to guarantee deterministic, side-effect-free execution during the recursive iteration. Functions such as NEWID(), RAND(), or any user-defined function that modifies state, writes to the database, or relies on non-deterministic behavior are disallowed in the recursive member of the CTE. SQL Server 2025’s query optimizer specifically validates the recursive anchor and recursive member separately, flagging any call to a function that could produce inconsistent results across recursive invocations.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Function Call

  • Open the query that triggered Error 464 in SQL Server Management Studio (SSMS) 21 or Azure Data Studio.
  • Review the recursive member (the part of the UNION ALL after the anchor query) line by line.
  • Look specifically for calls to non-deterministic or side-effecting functions like NEWID(), GETDATE(), RAND(), NEXT VALUE FOR, or custom CLR/scalar UDFs that perform I/O or state mutation.

Step 2: Move Side-Effect Logic Outside the Recursive Member

  • Refactor the query so that any non-deterministic value (e.g., a GUID or timestamp) is generated in the anchor member only, then propagated downward through the recursion using a column reference instead of a fresh function call.
  • Example fix: instead of calling NEWID() inside the recursive part, add a column like RowGuid in the anchor SELECT and carry it forward using cte.RowGuid in the recursive SELECT.
  • If a scalar UDF is causing the issue, verify its determinism using SELECT OBJECTPROPERTY(OBJECT_ID('YourFunctionName'), 'IsDeterministic') in a query window.

Step 3: Rewrite Using a Temporary Table or WHILE Loop (If Refactor Isn’t Possible)

  • If the recursive logic genuinely requires a non-deterministic operation at each step, abandon the recursive CTE approach.
  • Use a WHILE loop with a temporary table (#TempResults) to iterate and insert rows procedurally, allowing full use of side-effecting functions per iteration.
  • Store intermediate values with INSERT INTO #TempResults SELECT ... inside the loop, then query the final result set once looping completes.

Step 4: Validate Function Determinism Settings

  • Check if any custom CLR functions are marked with IsDeterministic = false in their assembly metadata.
  • Run sp_helptext 'YourFunctionName' to inspect the function body for hidden calls to system functions that break determinism.
  • Update the function definition and redeploy the assembly if necessary via ALTER ASSEMBLY.

Alternative or Verification Steps

  • Re-run the original query after refactoring; confirm Error 464 no longer appears in the Messages tab of SSMS 21, and check the Windows Server 2025 Event Viewer (Application log) to ensure no related SQL Server exceptions were logged.
  • As an alternative, convert the recursive CTE into a stored procedure using a cursor or WHILE loop, which fully supports non-deterministic function calls at each iteration without triggering Error 464.