Example - Use of Cross Apply Operator

We will look at simple example using CROSS Apply operator. Using Cross Apply Operator to JOIN table returned by function. Example below lists an example step by step

use tempdb

DROP TABLE Employee
DROP TABLE ADDRESS

--STEP 1
CREATE TABLE Employee
(
     NAME VARCHAR(20),
     Id INT Primary Key
)

--STEP 2

CREATE TABLE ADDRESS
(
   Id INT Foreign Key References Employee(Id),
   Location VARCHAR(100),
   Isactive bit
)

--STEP 3

INSERT INTO Employee (NAME,Id)
VALUES ('Ram',1)
INSERT INTO Employee (NAME,Id)
VALUES ('Sri',2)

--STEP 4

INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (1,'Chennai',1)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (1,'Bangalore',0)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (2,'Bangalore',1)
INSERT INTO ADDRESS (ID,Location,Isactive)
VALUES (2,'Delhi',0)

--STEP 5

SELECT * FROM ADDRESS

--STEP 6
CREATE Function dbo.getaddress (@id int)
RETURNS
@ADDRESS TABLE
(
   Id int,
   location varchar(100)
)
AS
BEGIN
   IF ISNULL(@id,0) = 0
     BEGIN
       RETURN
     END
   INSERT INTO @ADDRESS(Id, location)
   SELECT ID, Location
   FROM ADDRESS
   WHERE Isactive = 1
   AND Id = @id
RETURN
END

--STEP 7

--Function and Cross Apply
SET STATISTICS IO ON
SELECT * FROM
dbo.Employee T1 CROSS APPLY
dbo.getaddress(T1.Id)

--Table '#1CF15040'. Scan count 2, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

--Table 'Employee'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT * FROM

dbo.Employee T1 JOIN ADDRESS AD
ON T1.Id = AD.Id
WHERE AD.Isactive = 1
--Table 'Employee'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'ADDRESS'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SCAN Count is Zero for employee table. Reason Here. Both inputs to nested loop are "SEEK"s, meaning you should usually see zero scan count.

Reference - Interpreting IO Statistics

To Touch on Basics Again

• Scalar Functions - Returns Single Variable
• Inline Table Value Function - Returns Table
• MultiStatement TVF - Return Table as defined in Schema

Good link on Difference between Functions and Stored Procedures Pinal Article - link

Happy Reading!!