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

by

Why This Error Happens

Error 5309 occurs because SQL Server’s query optimizer does not allow windowed functions (functions using the OVER() clause, such as ROW_NUMBER(), RANK(), SUM() OVER(), etc.) to be evaluated directly inside WHERE, HAVING, GROUP BY, or ON clauses. This is a T-SQL language restriction, not a server configuration or permissions issue—windowed functions can only be referenced in the SELECT list or the ORDER BY clause. The engine throws this error during query parsing/binding because it cannot logically resolve the window frame before the result set rows are filtered or grouped.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Clause

  • Open the query that triggered Error 5309 in SQL Server Management Studio (SSMS) or Azure Data Studio.
  • Look for any OVER() function (e.g., ROW_NUMBER() OVER (PARTITION BY ...)) placed inside a WHERE, HAVING, GROUP BY, or JOIN ... ON clause.
  • Confirm the exact error text: “Windowed functions can only appear in the SELECT or ORDER BY clauses.”

Step 2: Refactor the Query Using a CTE or Subquery

  • Move the windowed function into the SELECT list of an inner query, Common Table Expression (CTE), or derived table.
  • Apply your filtering logic in the outer query against the aliased column instead of the raw function call.
  • Example pattern:
WITH RankedData AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowNum
    FROM Orders
)
SELECT * FROM RankedData WHERE RowNum = 1;

Step 3: Validate Compatibility Level Settings

  • In SSMS, right-click the database → Properties → Options, and check the “Compatibility Level” field.
  • Ensure the database is running at compatibility level 160 (SQL Server 2022) or higher if using newer windowing syntax enhancements introduced in SQL Server 2025.
  • Run ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 170; if an older compatibility level is causing parser conflicts with newer window function syntax.

Step 4: Re-Deploy and Test the Corrected Query

  • Save the revised script and execute it in a test environment first.
  • Check SQL Server error logs (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) to confirm Error 5309 no longer appears after execution.

Alternative or Verification Steps

  • Run the corrected query with SET SHOWPLAN_TEXT ON; to confirm the execution plan compiles without binding errors before deploying to production.
  • If the error persists in complex nested queries, isolate each OVER() clause into its own CTE layer one at a time to pinpoint exactly which windowed function is misplaced.