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
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 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.