SQL 2005 APPLY
What if you have SQL Server table and would like to use one of the columns as an input to a Table Valued Function (TVF)? Well that depends on the version of SQL Server you have. In SQL 2000, you really couldn't. You would have to take each column value, and run the TVF separately. This is not a practical solution if your result set is of any significant size. BUT, in SQL Server 2005 there is a cool new operator called the APPLY operator. What the APPLY operator allows you to do is essentially join your table to a TVF (Or table, or view, but the TVF is the coolest use IMO).
Example:
SELECT
TOP 5 *
FROM
sys.dm_exec_query_stats qs
CROSS
APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS
APPLY sys.dm_exec_sql_text(qs.sql_handle)
ORDER
BY max_elapsed_time DESC
So what is with the "CROSS"? Well, there are 2 different types of APPLYs. If you think of APPLY like a JOIN, then the CROSS APPLY is like an INNER JOIN. You will only receive results for rows where the TVF returns a row for the column input. An OUTER APPLY is, surprisingly enough, like an OUTER JOIN. You will receive results for all Rows from the Outer table, and NULLS if no row is returned from the TVF.
This example Query is pretty cool itself, but I will get into that in another post.