The many uses of CROSS APPLY

2

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.

SELECT *
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 
		"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; AnnualSales[1]','decimal') AS AnnualSales,
	StoreInfo.StoreDetails.value('declare default element namespace 
		"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; AnnualRevenue[1]','decimal') AS AnnualRevenue
FROM Sales.Store Store
CROSS APPLY Store.Demographics.nodes('declare default element namespace 
		"http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey"; 
		/StoreSurvey') AS StoreInfo(StoreDetails)
GO

Then while reading this post “Is there a better option than Union All for multiple selects from the same row?“ on dba.stackexchange.com 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 http://www.sqlservercentral.com. 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, 
     UPPER(ComputedColumn.FullName)
FROM Person.Person Person
CROSS APPLY (SELECT ISNULL(Title+' ','')+ISNULL(FirstName+' ','')+
     ISNULL(MiddleName+' ', '')+ISNULL(LastName,'') AS FullName)
     ComputedColumn

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.

About these ads

2 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). […]

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 487 other followers

%d bloggers like this: