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

by

Troubleshooting Guide: SQL Server Error 4108 — “Windowed Functions Can Only Appear in the SELECT or ORDER BY Clauses”

Why This Error Happens

This error occurs because SQL Server’s T-SQL grammar strictly restricts the use of windowed functions (such as ROW_NUMBER(), RANK(), SUM() OVER(), or LAG()/LEAD()) to only the SELECT list or the ORDER BY clause of a query. When a developer attempts to reference a windowed function directly inside a WHERE, GROUP BY, HAVING, or JOIN ON clause, the query parser rejects the statement because the logical processing order of SQL evaluates those clauses before the SELECT list is resolved. Essentially, the database engine cannot filter or group by a value that hasn’t technically been calculated yet in the query’s execution pipeline.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Clause

  • Open the query in SQL Server Management Studio (SSMS) or Azure Data Studio and locate the exact line number referenced in the Error 4108 message.
  • Check if the windowed function (e.g., ROW_NUMBER() OVER (PARTITION BY...)) is sitting inside a WHERE or HAVING clause.
  • Look specifically for attempts to filter results immediately after calculating a rank or running total in the same query block.

Step 2: Restructure the Query Using a CTE or Subquery

  • Wrap the original query (including the windowed function in the SELECT list) inside a Common Table Expression (CTE) or a derived table (subquery).
  • Move the WHERE or HAVING logic to the outer query that selects from the CTE/subquery.
  • Example fix syntax:
  WITH OrderedCTE AS (
      SELECT *, ROW_NUMBER() OVER (ORDER BY SalesDate) AS RowNum
      FROM Sales
  )
  SELECT * FROM OrderedCTE WHERE RowNum = 1;

Step 3: Validate Compatibility Level Settings

  • Ensure the database is running on the correct compatibility level for SQL Server 2025 by checking sys.databases.
  • Run the following command to verify the current level: 
  SELECT name, compatibility_level FROM sys.databases WHERE name = 'YourDBName';
  • If necessary, update it using ALTER DATABASE YourDBName SET COMPATIBILITY_LEVEL = 170; to ensure modern windowing function support is fully enabled.

Step 4: Clear Cached Execution Plans

  • If the query logic is already correct but the error persists intermittently on Windows Server 2025, the Plan Cache might be holding a corrupted or outdated execution plan.
  • Execute DBCC FREEPROCCACHE; to clear the cache (note: use with caution on production servers during peak hours as this forces recompilation).
  • Restart the SQL Server service via services.msc if the issue persists after clearing the cache.

Alternative or Verification Steps

  • Verification: Re-run the query after refactoring; if the syntax check passes and returns a result set without a red syntax error flag in SSMS, the fix was successful.
  • Alternative: If restructuring the CTE is not feasible due to complex legacy code, use the APPLY operator (CROSS APPLY) instead of a WHERE clause to filter rows based on windowed function results in a single pass.