SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

You can’t use aggregate/windowing functions with CROSS APPLY

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.

CrossApplyxWindowing1

And here is the output from the CROSS APPLY query.

CrossApplyxWindowing2

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

CrossApplyxWindowing3


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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...