SQLServer Quiz 2011 - The best and bad uses of the TSQL APPLY operator
Answer:
What is APPLY operator?
The APPLY operator allows us to invoke a table valued function for each row returned by an outer expression query. Basically, SQL Server allows us to join table and table valued function in such a way that for each record returned from outer query, we can call table valued function to retrieve data. This operator was not available in versions prior to SQL Server 2005 and was done using a derived table/view. We can use APPLY in the FROM clause of a query, similar to the way we use the JOIN relational operator. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.APPLY operator can be used in two different ways and they are CROSS APPLY and OUTER APPLY.
CROSS APPLY: It is like an inner join. Returns only those rows from the outer query, for which table valued function returns some data.
OUTER APPLY: Its behavior is similar to outer join. It returns all the rows from the outer query irrespective of data returned by table function. If table function returns data then column values pertaining to table function will be not null otherwise it will contain null values.
Example:
Let us take two tables Territories and Region as an example. Following query has same result set and execution plan is also the same.
--Using CROSS APPLY
SELECT * FROM Region r
CROSS APPLY
(
SELECT * FROM Territories t
WHERE t.RegionID = r.RegionID
) t1
GO
---Using INNER JOIN
SELECT * FROM Region r
INNER JOIN Territories t ON t. RegionID = r. RegionID
Now let us see where APPLY operator is really required in some situations. Let us create a table-valued function that accepts RegionID as its parameter and returns all the Territories that are belong to this region. Following is the table-valued function:
--Table-valued Function
CREATE FUNCTION dbo.fn_GetTerritoriesOfRegion(@RegionID AS INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Territories t
WHERE t.RegionID = @RegionID
)
GO
The following query selects data from Region table and uses CROSS APPLY to join with the function we created. It passes the RegionID for each row from the outer table expression , in our exxample, Region table and evaluates the function for each row similar to a correlated subquery.
SELECT * FROM Region r
CROSS APPLY dbo.fn_GetTerritoriesOfRegion(r.RegionID)
GO
The following query uses the OUTER APPLY in place of CROSS APPLY and hence unlike CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing columns.
SELECT * FROM Region r
OUTER APPLY dbo.fn_GetTerritoriesOfRegion(r.RegionID)
GO
Now let us try using traditional join to query the table-valued function.
SELECT * FROM Territories t
INNER JOIN fn_GetTerritoriesOfRegion(r.RegionID) r ON 1=1
We get following error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "r.RegionID" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "r.RegionID" could not be bound.
This is because with JOINs the execution context of outer query is different from the execution context of the function, and we cannot bind a value or variable from the outer query to the function as a parameter. Hence the APPLY operator is required for such queries.
Therefore, APPLY operator is required when you have to use table-valued function in the query, but it can also be used with an inline SELECT statements.
The main advantages using APPLY Operator are:
- Allows us to join two table expressions.
- Use of APPLY is necessary if we have table-valued expression on right part.
- Use of APPLY operator boost the performance of our query.
Traditional operators are used when there are no table-valued function and when the performance issue is acceptable.
Reference: http://msdn.microsoft.com/en-us/library/ms345147(v=sql.90).aspx
Thank you,
Abi Chapagai