Dynamic Management Objects/ Syndicated

CROSS APPLY

sqlagentman CROSS APPLY, SQL 4 Comments Jan 27, 2010

On Twitter this morning I saw a plea for an explanation on what CROSS APPLY is in Transact-SQL.  I answered the question via email and then realized that it may make for a post here on The SQL Agent Man…

When used, CROSS APPLY will execute the function listed in the CROSS APPLY sub-clause of the FROM statement in your T/SQL command for each row being returned in the query.  An example is probably easier to see the behavior:

This query will return a row for each active request on your SQL instance:

SELECT [session_id], [request_id], [user_id], [sql_handle] 
FROM sys.dm_exec_requests 
WHERE [session_id] > 50

Great, but what the heck is the sql_handle?  Well, it’s a unique identifier for the T/SQL text executing on a SQL Server.  It’s a bit cryptic, don’t you think?  Well, you can return the plain text of a sql_handle via the sys.dm_exec_sql_text() DMF by passing in the sql_handle as follows:

 SELECT * 
FROM sys.dm_exec_sql_text(0x0200000036FF532C48B2041F90C3E1269468AE086BDC1727);

But why would you want to cut/paste the sql_handle?  What if there are 3000 active requests, not just 1?  Well, by using CROSS APPLY it will run for each row in the result set and give you the following (I am just interested in the [text] column so you’ll see that I only ask to return that row from sys.dm_exec_sql_text (as DEST) in my SELECT statement:

SELECT DER.[session_id], DER.[request_id], DER.[user_id], DER.[sql_handle], DEST.TEXT 
FROM sys.dm_exec_requests DER
     CROSS APPLY sys.dm_exec_sql_text(DER.[sql_handle]) DEST
WHERE [session_id] > 50

As you can see, the CROSS APPLY results in the function call to the sys.dm_exec_sql_text being executed against each row in sys.dm_exec_requests by passing in the sql_handle from that Dynamic Management View.