Troubleshooting Guide: Error 10759 — “Use of DISTINCT is Not Allowed with the OVER Clause”

Why This Error Happens

This error occurs because SQL Server’s T-SQL syntax explicitly disallows combining the DISTINCT keyword with window functions that use the OVER clause (e.g., COUNT(DISTINCT column) OVER (...)). This is a parser-level restriction in the SQL Server 2025 Database Engine rather than a configuration, permissions, or file corruption issue. The query is rejected during the parsing/compilation phase before execution ever begins, meaning the fix must happen in the T-SQL code itself, not in server settings.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Query

• Locate the exact query in your application, stored procedure, or report that triggered Error 10759.
• Check SQL Server Management Studio (SSMS) or Azure Data Studio error output for the line number referenced.
• Look specifically for patterns like SUM(DISTINCT col) OVER (PARTITION BY ...) or COUNT(DISTINCT col) OVER (ORDER BY ...).

Step 2: Rewrite the Query Using a CTE or Subquery

• Replace the DISTINCT + OVER combination with a Common Table Expression (CTE) that pre-aggregates distinct values first.
• Example pattern:

  WITH DistinctValues AS (
      SELECT DISTINCT CustomerID, OrderAmount
      FROM Sales.Orders
  )
  SELECT CustomerID,
         SUM(OrderAmount) OVER (PARTITION BY CustomerID) AS TotalAmount
  FROM DistinctValues;

• This removes duplicates in a separate step before the window function is applied.

Step 3: Use APPLY or GROUP BY as an Alternative

• For scenarios needing distinct counts, consider using OUTER APPLY or CROSS APPLY with a correlated subquery instead.
• Example:

  SELECT o.OrderID,
         da.DistinctCount
  FROM Sales.Orders o
  OUTER APPLY (
      SELECT COUNT(DISTINCT CustomerID) AS DistinctCount
      FROM Sales.Orders o2
      WHERE o2.RegionID = o.RegionID
  ) da;

• This avoids the parser restriction entirely while achieving the same analytical result.

Step 4: Validate Compatibility Level Settings

• Confirm the database compatibility level under Database Properties > Options > Compatibility Level in SSMS.
• Even on SQL Server 2025, legacy compatibility levels (e.g., 130 or lower) do not change this specific restriction, but verifying ensures no other syntax-related errors are masked.
• Run SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabaseName'; to confirm the current setting.

Alternative or Verification Steps

• After rewriting the query, execute it in SSMS or Azure Data Studio and confirm it returns results without raising Error 10759.
• If the issue persists, use SET SHOWPLAN_TEXT ON to review the execution plan and ensure no nested DISTINCT/OVER combinations remain in dynamically generated SQL (common in ORMs like Entity Framework).