Thursday 17 May 2012

CROSS APPLY on SQL 2005



A new feature of Microsoft SQL Server 2005 is "CROSS  APPLY". It restricted "INNER JOIN" between a table (outer query) and a table-valued function (common usage), or derived table from correlated subquery. The table-valued function is evaluated only for the parameter values supplied by the outer query.

The result of the CROSS APPLY quay can be achieved by using temporary table, CTE or Table Variable. But CROSS APPLY provides a very powerful solution in a single query.


Let's takes an example, which can help us to build the concepts of CROSS APPLY.  Our motto is to supply the result of outer query as a parameters value of table value function.

[ Outer Query]  à result of outer query à [ Parameters value of Table valued Function]

Step-1 [ Create the Table Objects ]

CREATE TABLE People_details
(
   PersonID   int         NOT NULL,
   MotherID   int         NULL,
   FatherID   int         NULL,
   Name       varchar(50) NOT NULL,
   CONSTRAINT PK_People PRIMARY KEY(PersonID),
)
GO


Step-2 [ Insert Values Into Table Objects ]

INSERT INTO People_details
VALUES(, NULL, NULL, 'Rajesh Das')

INSERT INTO People_details
VALUES(, NULL, NULL, 'Raja Barma')

INSERT INTO People_details
VALUES(, NULL, NULL, 'Anand Kimmel')

INSERT INTO People_details
VALUES(, NULL, NULL, 'Sajan Kimmel')

INSERT INTO People_details
VALUES(, 2, 1,       'Joga Benavides')

INSERT INTO People_details
VALUES(, 3, 4,       'Giban Kimmel')

INSERT INTO People_details
VALUES(, 5, 6,       'Kalyan Hemenway')

INSERT INTO People_details
VALUES(, 5, 6,       'Dinesh Kimmel')

INSERT INTO People_details
VALUES(, 5, 6,       'Deb Kimmel')

INSERT INTO People_details
VALUES(10 , 5, 7,       'Rohit Benavides')

INSERT INTO People_details
VALUES(11 , 5, 7,       'Nitin Benavides')

INSERT INTO People_details
VALUES(12 , 5, 6,       'Jana Kimmel')

INSERT INTO People_details
VALUES(13 , 5, 6,       'Palu Kimmel')

INSERT INTO People_details
VALUES(14 , NULL, NULL, 'Logar Kimmel')

INSERT INTO People_details
VALUES(15 , NULL, NULL, 'David Benavides')

INSERT INTO People_details
VALUES(16 , 14, 13,     'Alex Kimmel')

INSERT INTO People_details
VALUES(17 , 14, 13,     'Noah Kimmel')

SELECT * FROM People_details


Step-3 [ The T-SQL Statements that I used in Function ]

SELECT p1.Name as MyName,
       p2.Name AS Mother,
       p3.Name As Father
FROM   People_details p1
       LEFT JOIN People_details p2 ON p1.MotherID = p2.PersonID
       LEFT JOIN People_details p3 ON p1.FatherID = p3.PersonID



Step-4 [ Create the Function ]

CREATE FUNCTION [fnGetParents](@PersonID int)
RETURNS @Parents TABLE
(
   [PersonID] [int] PRIMARY KEY NOT NULL,
   [Self]     [varchar](25),
   [Mother]   [varchar](25) NULL,
   [Father]   [varchar](25) NULL
)
AS
BEGIN
   INSERT INTO @Parents
   SELECT
      p1.PersonID,
      p1.Name AS [Self],
      p2.[Name] AS Mother,
      p3.[Name] AS Father
   FROM
      People_details p1
      INNER JOIN People_details p2 ON p1.MotherID = p2.PersonID
      INNER JOIN People_details p3 ON p1.FatherID = p3.PersonID
   WHERE
      p1.PersonID = @PersonID;
   RETURN;
END;

Step-5 [ This Query give you an error that's why we have to use CROSS APPLY ]

SELECT p1.PersonID, p1.Name, dbo.fnGetParents(p1.PersonID)
FROM People_details p1

Step-6 [ The CROSS APPLY ]

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father
FROM People_details p1
CROSS APPLY fnGetParents(p1.PersonID)p2


Analyzing the Step-6 that is the CROSS APPLY


[ Outer Query]  à result of outer query à [ Parameters value of Table valued Function]

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father
FROM People_details p1
CROSS APPLY fnGetParents(p1.PersonID)p2

Lets brake down the query into parts to understand it properly as above definition of CROSS APPLY says.

Here the Outer Query is

SELECT p1.PersonID, p1.Name, p2.Mother, p2.Father
FROM People_details p1

The Result of the Outer Query is

p1.PersonID

Parameters value of the Table valued Function is

fnGetParents(p1.PersonID)p2


I hope you now you understand it and thanking you to provide your valuable time on it.

Posted by: MR. JOYDEEP DAS

No comments:

Post a Comment