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

by

Troubleshooting Guide: SQL Server Error 8711 – Incompatible Orderings in Aggregate Functions

Why This Error Happens

Error 8711 occurs when a single query contains multiple ordered aggregate functions (such as those using WITHIN GROUP (ORDER BY ...)) that specify conflicting sort directions or sort columns within the same scope. SQL Server’s query optimizer cannot generate a single execution plan that simultaneously satisfies multiple incompatible sort requirements for aggregates evaluated in the same context. This is typically a query design issue rather than a server configuration problem, often introduced after application updates, ORM-generated queries, or manually written T-SQL that combines functions like STRING_AGG, PERCENTILE_CONT, or custom CLR aggregates with differing ORDER BY clauses.

Step-by-Step Resolution Guide

Step 1: Identify the Conflicting Aggregate Functions

  • Review the query that triggered Error 8711 in SQL Server Management Studio (SSMS) or Azure Data Studio.
  • Look specifically for multiple instances of WITHIN GROUP (ORDER BY ...) in the same SELECT statement or subquery scope.
  • Check for mismatched sort directions (e.g., one aggregate using ASC and another using DESC on related columns).
  • Example of a problematic pattern:
  SELECT 
      STRING_AGG(Name, ',') WITHIN GROUP (ORDER BY Name ASC),
      PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary DESC) OVER ()
  FROM Employees

Step 2: Isolate Each Aggregate Into Separate Query Scopes

  • Break the single query into separate Common Table Expressions (CTEs) or subqueries so each ordered aggregate operates independently.
  • Use a CTE per aggregate function to avoid scope collisions:
  WITH SortedByName AS (
      SELECT STRING_AGG(Name, ',') WITHIN GROUP (ORDER BY Name ASC) AS NameList
      FROM Employees
  ),
  SortedBySalary AS (
      SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary DESC) AS MedianSalary
      FROM Employees
  )
  SELECT * FROM SortedByName CROSS JOIN SortedBySalary;
  • Save the corrected script to a working file, e.g., C:\SQLScripts\Fix_8711_Query.sql.

Step 3: Align Sort Directions Where Aggregates Must Coexist

  • If the aggregates must remain in the same scope (e.g., inside a single OVER() window), standardize the ORDER BY direction across all ordered aggregates.
  • Update all conflicting clauses to use the same direction (either all ASC or all DESC) on the shared partition.
  • Re-run the query in SSMS to confirm the parser no longer throws Error 8711.

Step 4: Update Statistics and Rebuild the Execution Plan Cache

  • Clear any stale cached execution plans that may reference the old, broken query structure.
  • Run the following in a query window connected to the affected database:
  DBCC FREEPROCCACHE;
  GO
  UPDATE STATISTICS dbo.Employees WITH FULLSCAN;
  GO
  • Restart the SQL Server service via services.msc on Windows Server 2025 (locate “SQL Server (MSSQLSERVER)”) to ensure a clean plan cache state.

Step 5: Validate Application-Layer Query Generation (If Using an ORM)

  • If the query originates from Entity Framework, Dapper, or another ORM, inspect the generated SQL in the application logs.
  • Check configuration files (e.g., appsettings.json or web.config) for any custom aggregate mapping logic that might auto-generate conflicting ORDER BY clauses.
  • Patch the ORM query builder or override it with a raw SQL call using the corrected syntax from Step 2 or Step 3.

Alternative or Verification Steps

  • Verify the fix by re-executing the original failing query in SSMS and confirming it returns results without raising Error 8711; also check the SQL Server Error Log (C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG) for recurrence.
  • As an alternative, if restructuring the query isn’t immediately feasible, compute each ordered aggregate in separate round-trip queries from the application layer and merge the results in application code rather than in T-SQL.