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

The many uses of CROSS APPLY

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 
		"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 
		/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 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, 
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.

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


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.


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

Loading comments...