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

by

Troubleshooting Guide: SQL Server Error 5364

Why This Error Happens

This error occurs when a T-SQL query uses a window function with an explicit frame specification (ROWS BETWEEN... or RANGE BETWEEN...) but omits the required ORDER BY clause within the OVER() clause. SQL Server 2025’s query parser cannot determine a logical sequence for the rows in the window partition without an ordering definition, making the frame boundaries (such as UNBOUNDED PRECEDING or CURRENT ROW) meaningless. This is a syntax/logic validation failure rather than a permissions or connectivity issue, and it typically surfaces after migrating legacy T-SQL code or during the development of new analytical queries involving SUM() OVER(), AVG() OVER(), or similar aggregate window functions.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Open SQL Server Management Studio (SSMS) 21 or Azure Data Studio connected to your Windows Server 2025 instance.
  • Locate the exact line number referenced in the Error 5364 message in the Messages pane.
  • Look specifically for the keywords ROWS BETWEEN or RANGE BETWEEN within an OVER() clause.

Step 2: Insert a Valid ORDER BY Clause

  • Modify the OVER() clause to include an ORDER BY statement immediately before the ROWS or RANGE keyword.
  • Example of broken syntax: SUM(SalesAmount) OVER (PARTITION BY Region ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
  • Corrected syntax: SUM(SalesAmount) OVER (PARTITION BY Region ORDER BY OrderDate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
  • Ensure the column used in ORDER BY (e.g., OrderDate or a unique ID) logically defines the sequence of the dataset.

Step 3: Validate Data Types for Ordering

  • If using RANGE (as opposed to ROWS) with specific offsets, confirm that the ORDER BY column is a single column with a numeric or datetime data type.
  • Remove any composite (multi-column) ORDER BY statements if you are using RANGE BETWEEN with specific numeric boundaries, as SQL Server restricts RANGE frames to a single sort key.

Step 4: Recompile and Execute

  • Clear the execution plan cache for the specific query to ensure SQL Server 2025 validates the new syntax rather than pulling a corrupted cached plan: DBCC FREEPROCCACHE;
  • Run the corrected script via sqlcmd or SSMS to confirm the parser accepts the new window frame definition.

Alternative or Verification Steps

  • Verification: Execute the query and check that the result set populates the calculated window function column (e.g., running totals or moving averages) without throwing a red error banner in the SSMS output.
  • Alternative: If a logical order isn’t required for your business logic but the frame is necessary, use ORDER BY (SELECT NULL) as a placeholder to satisfy the syntax requirement while maintaining the original partition logic.