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

by

Why This Error Happens

Error 8728 occurs when a window function uses a RANGE frame specification (either explicitly or by default) while the ORDER BY clause references a column of a Large Object (LOB) data type, such as VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML, or TEXT/NTEXT/IMAGE. SQL Server’s query optimizer cannot logically compare and bound LOB values within a RANGE frame because these types do not have a fixed, comparable numeric or date boundary. This is a query design limitation rather than a server configuration or permissions issue, and it will persist regardless of indexing or hardware until the T-SQL syntax is corrected.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

  • Capture the exact T-SQL statement that triggered Error 8728 from SQL Server Management Studio (SSMS) or the Windows Event Viewer under Applications and Services Logs > MSSQLSERVER.
  • Look specifically for OVER (ORDER BY ... RANGE BETWEEN ...) clauses in the query text.
  • Confirm the data type of the column in the ORDER BY list using sp_help 'YourTableName' or by checking sys.columns.

Step 2: Replace RANGE with ROWS

  • Since ROWS frames do not require logical value-based comparisons, switch the window frame type from RANGE to ROWS.
  • Example fix:
  SELECT OrderID, OrderNotes,
         SUM(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
  FROM Orders;
  • Ensure the business logic (e.g., ties handling) remains valid, as ROWS evaluates physical row position rather than value-based ranges.

Step 3: Cast or Remove the LOB Column from ORDER BY

  • If the ORDER BY column itself must remain a LOB type, cast it to a bounded type (e.g., CAST(column AS VARCHAR(8000))) only if truncation risk is acceptable.
  • Alternatively, restructure the query to sort by a separate non-LOB column (such as OrderDate or an INT surrogate key) instead of the LOB field.
  • Avoid casting large XML or MAX-type fields directly into the ORDER BY of a windowed aggregate function.

Step 4: Validate Against SQL Server 2025 Compatibility Level

  • Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDB'; to confirm the database compatibility level.
  • If the database was upgraded from an older SQL Server version, confirm that legacy query patterns using implicit RANGE defaults are updated to explicit ROWS syntax for consistency with SQL Server 2025 engine behavior.

Alternative or Verification Steps

  • Re-run the corrected query in SSMS or Azure Data Studio and confirm Error 8728 no longer appears in the Messages tab.
  • As an alternative, wrap the window function in a Common Table Expression (CTE) that pre-selects and casts the LOB column to a non-LOB type before applying the OVER() clause, then verify the execution plan shows no window spool errors.