One of my favorite features of CROSS APPLY is the ability to use it instead of a calculated variable. Well recently I was working on performance tuning a rather annoying query (which I will blog about in more detail later) and one of the steps I took was an attempt to combine CROSS APPLY and a windowing function. As you can tell from the title it didn’t work.

-- Setup code 
CREATE TABLE CrossApplyWindow ( 
Code char(3), 
RegionId char(3) 
) 
GO 
 
INSERT INTO CrossApplyWindow VALUES  
('abc','123'), ('def','123'), ('ghi','123'), 
('abc','456'), ('ghi','456'), 
('abc','789'), ('def','789'), ('ghi','789'), ('jkl','789') 
GO 

The initial query was pulling a distinct count of codes by regionid.

SELECT RegionId, COUNT(DISTINCT Code) AS Calc 
FROM CrossApplyWindow 
GROUP BY RegionId; 
GO

Simple enough but in this particular case I wanted to do it using a windowing function. Unfortunately COUNT DISTINCT doesn’t work with windowing functions. (Side note: it would be nice if you voted for the connect item in the link.) So after getting some help I ended up using DENSE RANK() and MAX(). Interestingly enough in the case of my query it was much more efficient. My example here is a simplified version of the query, so it may not show any performance improvement. I haven’t actually checked.

SELECT  
    RegionId,  
    Calc = MAX(dr)  
FROM 
( 
    SELECT   
        RegionId, 
        dr = DENSE_RANK() OVER  
                (PARTITION BY RegionId ORDER BY Code) 
    FROM CrossApplyWindow 
) AS X 
GROUP BY RegionId; 
GO

So far so good. As part of my tuning effort I tried to get rid of the subquery by using CROSS APPLY.

SELECT RegionId, MAX(x.dr) AS Calc 
FROM CrossApplyWindow 
CROSS APPLY (SELECT DENSE_RANK() OVER  
    (ORDER BY Code) AS dr) x 
GROUP BY RegionId; 
GO

It certainly looks better right? Well here is the output from the previous query.

And here is the output from the CROSS APPLY query.

That doesn’t look right does it? Calc is only showing up as 1 for each region when obviously from the data and the previous query we can see that these results aren’t correct. The best I can tell is that CROSS APPLY only receives the row it is associated with. So no windowing functions (or aggregate functions for that matter) will work with it. Here is the best proof I could come up with.

SELECT CrossApplyWindow.*, x.* 
FROM CrossApplyWindow 
CROSS APPLY (SELECT Code, RegionId) x; 
GO

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, CROSS APPLY, language sql, microsoft sql server, sql statements, T-SQL