This past Saturday I presented my T-SQL Window Function Performance session at the Atlanta SQL Saturday. There were some interesting questions from the audience that didn’t actually pertain to performance. I didn’t have all the answers since there were some questions about things I hadn’t tried.

One question was about problems with using DISTINCT and windowing functions. I’m assuming that the question pertained to window aggregates, because when I experimented later with a window aggregate example, I received this error “Use of DISTINCT is not allowed with the OVER clause.”

Here is the query I tried:

SELECT COUNT(DISTINCT CustomerID) OVER(), CustomerID,     SalesOrderID
FROM sales.SalesOrderHeader
ORDER BY CustomerID;

To get around this, you could just use a CTE to get your distinct count and not use a windowing function at all. Here is a possible solution:

;WITH Cust AS(SELECT COUNT(DISTINCT CustomerID) AS CountOfCust
FROM Sales.SalesOrderHeader)
SELECT CountOfCust,
SOH.CustomerID, SalesOrderID
FROM Sales.SalesOrderHeader AS SOH
CROSS JOIN Cust;

Since window aggregate performance is not great, you may want to take this alternate approach anyway.  I keep learning new things about windowing functions, and I’ll also keep passing along what I learn.