Tags

,


APPLY operator is a new feature in SQL Server 2005. To use the APPLY, the database compatibility level must be at least 90. You can check the compatibility level of a database by running the below SQL command.

sp_dbcmptlevel ‘Database_Name’.

The above command returns message like “The current compatibility level is 90.” or  “The current compatibility level is 80.” Values of compatibility levels and their corresponding MS SQL Server versions are as follows:

60= SQL Server 6.0
65= SQL Server 6.5
70= SQL Server 7.0
80= SQL Server 2000
90= SQL Server 2005
100= SQL Server 2008

Now coming back to the APPLY operator, so the minimum compatibility level of the database should be 90.
The APPLY operator can be used on a table-valued function to be invoked for each row returned by the outer table expression.
There can be two forms of APPLY: CROSS APPLY and OUTER APPLY.CROSS APPLY is like INNER JOIN. It returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns all rows that produce a resultset with NULL values in the columns for the table-valued function.

The following example returns all the records of the customer table matching with cust.Customer_ID.

CREATE TABLE Customer (Customer_ID INT, Customer_Name Varchar(500))

CREATE TABLE Orders (Order_ID INT IDENTITY (1,1), Customer_ID INT, Order_Date DATETIME)

— creating a table-valued function
CREATE FUNCTION fn_getCustInfo(@CustID INT)
RETURNS TABLE
AS
RETURN

(
SELECT * FROM ORDERS
WHERE Customer_ID= @CustID
)

— Use APPLY
SELECT * FROM Customer Cust CROSS APPLY fn_getCustInfo(Cust.Customer_ID)
ORDER BY Cust.Customer_Name

LIMITATION: Although this is a good feature, there is some performance issue. If the outer table expression of the APPLY operator returns a huge resultset, then the performance of the query may take a hit due to the number of calls to the table-valued function.

Cheers,
Subhro Saha

Advertisements