Home | About Nakul Vachhrajani | Archives
April 28, 2011 9:00 AM
This is in continuation to my series on the Underappreciated features of Microsoft SQL Server. The series has been inspired from Andy Warren’s editorial on SQLServerCentral.com of the same name.
Today, we will look at a great new T-SQL enhancement introduced since SQL Server 2005 – the APPLY operator. Per Books On Line, “the APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.”
Instead of joining two tables, when APPLY is used, we join the output of a table-valued function with the outer table – such that the each row in the output of the table valued function is evaluated for each row of the outer table.
Just as we have multiple forms of the JOIN operator, we also have two forms of the APPLY operator – CROSS APPLY and OUTER APPLY. The difference is quite simple:
I believe the most common use of APPLY outside of a business application, is in performance tuning and database administration. One of the things that DBA are always monitoring is the answer to the question – “Which queries are currently running against a particular SQL Server?”. The simple query for this is:
SELECT * FROM sys.dm_exec_requests ser CROSS APPLY sys.dm_exec_sql_text(ser.sql_handle)
As you can see, the query is such that the sql_handle is taken from the DMV – sys.dm_exec_requests and then applied to the function – sys.dm_exec_sql_text. Because we do not want NULL values, we used CROSS APPLY. As an exercise, try using OUTER APPLY and see what happens.
For a more examples, I would redirect the reader to Books On Line at: http://technet.microsoft.com/en-us/library/ms175156.aspx. The example and explanation is excellent, and very easy to understand.
So, one might say that if the output of the table valued function was an actual table, CROSS APPLY can be replaced by a CROSS JOIN. However, that is not entirely true. CROSS JOIN will produce a Cartesian product, hence, if the outer table has m rows and the inner table n, the output will be (m x n) rows. CROSS APPLY, on the other hand, is more similar to an INNER JOIN.
Finally, let me draw your attention to a few things that you should keep in mind before using CROSS APPLY:
Until we meet next time,
Be courteous. Drive responsibly.
Posted by nakulvachhrajani
Categories: #SQLServer, Blog, Imported from BeyondRelational
Tags: #SQLServer
Mobile Site | Full Site
Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.
