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.

The two forms of APPLY

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:

1. CROSS APPLY only returns rows from the outer table which produce a result set from the table valued function
2. OUTER APPLY returns both rows – irrespective of whether or not they produce a result set. NULL values are seen for the output of the table valued function for such rows

Examples

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.

The big difference – CROSS APPLY v/s CROSS JOIN

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.

Some things to keep in mind

Finally, let me draw your attention to a few things that you should keep in mind before using CROSS APPLY:

• It’s quite obvious that to use the APPLY operator, the compatibility level of the database must at least be 90
• Performance impact – It is quite clear that we will have at least one scan every time the TVF is executed. Hence, please keep an eye out on performance aspects before jumping in and using CROSS APPLY in everything – moderation is always good

Until we meet next time,

Be courteous. Drive responsibly.