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.

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