The many uses of CROSS APPLY


May 20, 2013 by Kenneth Fisher

Over the last few years of studying SQL I’ve noticed 4 different uses for the command CROSS APPLY.

In the first use I ever saw, and certainly the one I see the most commonly, CROSS APPLY is used to run a function for each row of the query. This is also the easiest use to find in BOL. Here is a very common example using a DMV and a DMF.

FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Next while studying XML (see A review of SQL Interoperability / Joes 2 Pros Volume 5 by Rick A Morelan ) I found out that you can use it to shred XML.

SELECT Store.Name, 
	StoreInfo.StoreDetails.value('declare default element namespace 
		""; AnnualSales[1]','decimal') AS AnnualSales,
	StoreInfo.StoreDetails.value('declare default element namespace 
		""; AnnualRevenue[1]','decimal') AS AnnualRevenue
FROM Sales.Store Store
CROSS APPLY Store.Demographics.nodes('declare default element namespace 
		/StoreSurvey') AS StoreInfo(StoreDetails)

Then while reading this post “Is there a better option than Union All for multiple selects from the same row?“ on I saw an example of using CROSS APPLY to unpivot a table. I was stunned at how simple it was. To make sure I understood it well enough to remember when I needed it, and to share something I felt was pretty cool, I posted a blog entry “UNPIVOT a table using CROSS APPLY“. Later I found this great article on An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

SELECT UnPivotMe.FirstName, UnPivotMe.LastName, 
        CrossApplied.Question, CrossApplied.Answer
FROM UnPivotMe
CROSS APPLY (VALUES (Question1, Answer1),
                    (Question2, Answer2),
                    (Question3, Answer3),
                    (Question4, Answer4),
                    (Question5, Answer5)) 
            CrossApplied (Question, Answer)

And last but certainly not least I learned how to use The APPLY operator for reusable computations while watching Kendra Little’s 5 T-SQL Features You’re Missing Out On. Something I highly recommend watching.

SELECT Person.Title, Person.FirstName, 
     Person.MiddleName, Person.LastName, 
FROM Person.Person Person
CROSS APPLY (SELECT ISNULL(Title+' ','')+ISNULL(FirstName+' ','')+
     ISNULL(MiddleName+' ', '')+ISNULL(LastName,'') AS FullName)

Now I will admit I didn’t go into great detail on any of these topics, but I did try to provide at least a basic example, and a link or two where you can to get more in-depth information.

I’m truly amazed at the versatility of this operator. These are the four major uses (there are a number of permutations on each) that I’ve seen so far and I wouldn’t be surprised to find more. If you know another then please let me know in the comments. I’d love to add it to the list.

4 thoughts on “The many uses of CROSS APPLY

  1. […] this week I posted The many uses of CROSS APPLY and I’m quite glad I did. I’ve been working on a series of audit queries for the last […]

  2. […] The Many Uses of Cross Apply – An interesting looking at the various ways the Cross Apply function can be used, brought together courtesy of Kenneth Fisher (Blog). […]

  3. […] and (ABS(CHECKSUM(NEWID()) % 5)) to generate a random number number of rows. I’m also using CROSS APPLY because that will call the subquery once for each row returned by the outer query. At least […]

  4. […] love CROSS APPLY. I also love OUTER APPLY. What’s the difference though? The same difference as with an INNER […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: