Ada Lovelace: All the Fuss about CROSS APPLY .

By Kalman Toth, M.Phil. Physics, M.Phil. Computing Science, MCDBA, MCITP
March 14 , 2011
Since 2004 there is buzz in SQL circles about CROSS APPLY, a new feature of SQL Server 2005. Actually, CROSS APPLY is quite simple: a 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 paramater values supplied by the outer query. Here is a quick example for CROSS APPLY usage applying a DMV and a DMF:

-- SQL Cross Apply quick syntax - SELECT * last ran stats

SELECT deqs.last_execution_time AS LastRun, dest.TEXT AS QueryText, *

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

WHERE LEFT(dest.TEXT,8)='SELECT *'

ORDER BY LastRun DESC

The results generated by CROSS APPLY queries can be achieved without using CROSS APPLY by applying a temporary table, common table expression or table variables. However, that is a bit tedious. The application of CROSS APPLY in conjunction with a table-valued function yields a neat and very powerful solution in a single well structured query.
When we take the AdventureWorks2008 sample database product table we see that the ProductID-s are not continuous by color. The following exercise finds the missing ID-s for each color to make it a perfect sequence with the application of CROSS APPLY and nested CTE-s. The fnRange table-valued UDF generates a sequence between two integers. We use LEFT JOIN to find the "gap" numbers.

CREATE FUNCTION fnRange ( @Start int, @End int)

RETURNS TABLE AS

RETURN (SELECT * FROM (SELECT SEQ=ROW_NUMBER() OVER (ORDER BY (SELECT 1))

FROM MASTER.dbo.spt_values a

-- CROSS JOIN MASTER.dbo.spt_values b -- uncomment for more range

) x

WHERE SEQ BETWEEN @Start AND @End)

;WITH CTE (Color, MinID, MaxID) AS

(SELECT Color, MIN(ProductID), MAX(ProductID)

FROM Production.Product

WHERE Color is not null GROUP BY Color),

cteSEQ AS (SELECT Color, SEQ FROM CTE

CROSS APPLY dbo.fnRange (MinID, MaxID) as R)

SELECT *, P.Color, I.SEQ

FROM cteSEQ I LEFT JOIN Production.Product P

ON I.SEQ = P.ProductID AND I.Color = P.Color

AND P.Color is not null

WHERE P.ProductID is null

ORDER BY I.Color, I.SEQ

/* Missing from continuous ID sequence

Color SEQ

Black 317

Black 318

Black 319

Black 320

Black 321

.... */

In another article I mentioned that CROSSTAB reports are excellent career boosters for database developers and DBA-s. So are CROSS APPLY reports.

In the first three T-SQL examples we use derived table (as opposed to table-valued function) for CROSS APPLY.

------------
-- T-SQL column aliasing with CROSS APPLY
------------
SELECT TranID, ProdID, Qty, Cost, TotalCost=Qty * Cost
FROM AdventureWorks2008.Production.TransactionHistory
CROSS APPLY
(SELECT TranID = TransactionID
        ,ProdID = ProductID
        ,Qty = Quantity
        ,Cost = ActualCost ) x
WHERE Qty > 10 and Cost > 0.0
ORDER BY TranID, ProdID
/* TranID ProdID      Qty   Cost       TotalCost
100154      864         14    41.275      577.85
100157      869         24    45.4935     1091.844
100198      869         16    45.4935     727.896
....
*/

------------

-- CROSS APPLY using GROUP BY derived table from correlated subquery

------------

USE AdventureWorks;

DECLARE @Year INT,

@Month INT

SET @Year = 2003;

SET @Month = 2

-- SQL cross apply - SQL group by - SQL correlated subquery

SELECT [Customer] = s.Name,

-- Special money data type currency formatting option

[Total$ Sales] = '$' +

Convert(VARCHAR,Convert(MONEY,SalesAmount.OrderTotal),1)

FROM Sales.Customer AS c

-- The customer name is in this table

INNER JOIN Sales.Store AS s

ON s.CustomerID = c.CustomerID

-- The inner query is a correlated GROUP BY subquery

CROSS APPLY (SELECT soh.CustomerId,

Sum(sod.LineTotal) AS OrderTotal

FROM Sales.SalesOrderHeader AS soh

INNER JOIN Sales.SalesOrderDetail AS sod

ON sod.SalesOrderId = soh.SalesOrderId

-- This is the correlation to the outer query

WHERE soh.CustomerId = c.CustomerId

-- Filter data

AND Year(OrderDate) = @Year

AND Month(OrderDate) = @Month

GROUP BY soh.CustomerId) AS SalesAmount

ORDER BY [Customer]

/* Partial results

(132 row(s) affected)

Customer Total$ Sales

Ace Bicycle Supply $647.99

Affordable Sports Equipment $50,953.32

Alpine Ski House $939.59

Basic Sports Equipment $159.56

Bicycle Lines Distributors $22,243.33

/**** OUTER APPLY results: (701 row(s) affected) ********/

------------

-- CROSS APPLY using derived table from correlated subquery

------------

-- Create and populate tables for testing

USE tempdb;

SET NOCOUNT ON;

CREATE TABLE Account (

AccountID INT IDENTITY PRIMARY KEY,

AccountName VARCHAR(50),

CreateDate SMALLDATETIME DEFAULT (CURRENT_TIMESTAMP),

IsActive BIT DEFAULT (1))

CREATE TABLE Deposit (

DepositId INT IDENTITY PRIMARY KEY,

AccountID INT,

Amount MONEY,

DepositDate SMALLDATETIME DEFAULT (getdate()))

INSERT Account(AccountName) VALUES ('Charles Mills')

INSERT Account(AccountName) VALUES ('Miranda Vegas')

INSERT Account(AccountName) VALUES ('Corner Hardware')

INSERT Account(AccountName) VALUES ('Laptop Land')

INSERT Account(AccountName) VALUES ('Cellphone City')

SELECT * FROM Account

AccountID AccountName CreateDate IsActive

1 Charles Mills 2015-01-25 08:38:00 1

2 Miranda Vegas 2015-01-25 08:38:00 1

3 Corner Hardware 2015-01-25 08:38:00 1

4 Laptop Land 2015-01-25 08:38:00 1

5 Cellphone City 2015-01-25 08:38:00 1

INSERT Deposit (AccountID, Amount) VALUES(1, 1400.0)

INSERT Deposit (AccountID, Amount) VALUES(1, 1200.0)

INSERT Deposit (AccountID, Amount) VALUES(1, 1300.0)

INSERT Deposit (AccountID, Amount) VALUES(1, 1100.0)

INSERT Deposit (AccountID, Amount) VALUES(2, 400.0)

INSERT Deposit (AccountID, Amount) VALUES(2, 200.0)

INSERT Deposit (AccountID, Amount) VALUES(2, 300.0)

INSERT Deposit (AccountID, Amount) VALUES(2, 900.0)

INSERT Deposit (AccountID, Amount) VALUES(3, 33400.0)

INSERT Deposit (AccountID, Amount) VALUES(3, 11200.0)

INSERT Deposit (AccountID, Amount) VALUES(3, 22300.0)

INSERT Deposit (AccountID, Amount) VALUES(3, 12100.0)

-- CROSS APPLY returns only matching data - SQL cross apply

-- Cross apply T-SQL - Cross apply mssql - derived table - SQL correlated subquery

SELECT a.AccountName,

TopDeposit=tt.Amount

FROM ACCOUNT a

CROSS APPLY (SELECT TOP ( 1 ) Amount

FROM Deposit t

WHERE t.AccountID = a.AccountID

ORDER BY Amount DESC) tt

ORDER BY AccountName

AccountName TopDeposit

Corner Hardware 33400.00

Miranda Vegas 900.00

Charles Mills 1400.00

-- Cleanup

DROP TABLE tempdb.dbo.Account

DROP TABLE tempdb.dbo.Deposit

------------

In the following example, the table-valued inline function returns the top 5 (highest TotalDue) orders for a store or individual customer, provided there are 5 orders. The SELECT query itself is restricted to stores. CROSS APPLY joins the store information with the top 5 orders information produced by the table-valued function.

USE AdventureWorks;

-- SQL inline function

-- User-defined inline function

CREATE FUNCTION Sales.fnTopNOrders (

@CustomerID AS INT,

@n AS INT )

RETURNS TABLE

RETURN

SELECT

TOP(@n) SalesOrderID,

ShipDate = convert(char(10), ShipDate,112),

-- SQL currency formatting

TotalDue=convert(varchar,TotalDue,1)

FROM AdventureWorks.Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID

ORDER BY TotalDue DESC

-- SQL cross apply - SQL Server cross apply

SELECT

StoreName=s.Name,

[Top].ShipDate,

[Top].SalesOrderID,

TotalDue='$'+[Top].TotalDue

FROM AdventureWorks.Sales.Store AS s

JOIN AdventureWorks.Sales.Customer AS c

ON s.CustomerID = c.CustomerID

CROSS APPLY

AdventureWorks.Sales.fnTopNOrders(c.CustomerID, 5) AS [Top]

WHERE CustomerType='S'

ORDER BY StoreName, convert(money,TotalDue) DESC

Partial result set:

StoreName	ShipDate	SalesOrderID	TotalDue
A Bike Store	20020208	45283	$37,643.14
A Bike Store	20020508	46042	$34,722.99
A Bike Store	20011108	44501	$26,128.87
A Bike Store	20010808	43860	$14,603.74
A Great Bicycle Company	20010908	44125	$3,450.98
A Great Bicycle Company	20020308	45569	$2,828.58
A Great Bicycle Company	20011208	44793	$2,828.58
A Great Bicycle Company	20030308	49537	$622.95
A Great Bicycle Company	20031208	59009	$50.77
A Typical Bike Shop	20020608	46343	$39,156.33
A Typical Bike Shop	20011208	44755	$37,725.60

The CROSS APPLY operator is frequently used in joining DMVs with DMFs (Dynamic Management Views with Dynamic Management Functions). In the following examples the sys.dm_exec_sql_text DMF returns the source text for the cached plans and queries like CREATE PROCEDURE, SELECT..., CREATE VIEW, etc..

-- SQL cross apply - Cross apply mssql

-- SQL dynamic management view - dmv - SQL dynamic management function - dmf

SELECT LastExecutionTime = max(last_execution_time),

Query = Text

FROM sys.dm_exec_query_stats AS eqs

CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS SQL

WHERE Text LIKE ('%AdventureWorks%')

AND Text NOT LIKE ('%fullText%')

GROUP BY Text

ORDER BY Query

/* Partial results

LastExecutionTime Query

2009-01-24 09:42:23.217 SELECT EmployeeID, StaffName = LastName+',....

2009-01-24 10:31:49.170 SELECT e.EmployeeID, e.Title, StaffName = ....

-- SQL Server cross apply

SELECT

PlanSource=sql.text,

plans.*

FROM sys.dm_exec_cached_plans plans

CROSS APPLY sys.dm_exec_sql_text (plans.plan_handle) AS sql

The following CROSS APPLY query returns sql source and information about all executing requests within SQL Server:

-- SQL cross apply

SELECT

RequestSource = sql.text,

er.*

FROM

sys.dm_exec_requests er

CROSS APPLY

sys.dm_exec_sql_text(sql_handle) AS sqlGO

The following CROSS APPLY example script creates the Employee table in tempdb with select into from HumanResources tables in AdventureWorks. To be used with CROSS APPLY, the T-SQL script creates a table-valued orginazational chart subtree function by applying recursive CTE. The new tree-processing UDF is used in CROSS APPLY queries:

USE tempdb;

-- SQL select into create table - SQL inner join

SELECT e.EmployeeID, e.Title, StaffName = LastName+', '+FirstName,

ManagerID, Department=d.Name

INTO Employee

FROM AdventureWorks.HumanResources.Employee e

INNER JOIN AdventureWorks.HumanResources.EmployeeDepartmentHistory edh

ON e.EmployeeID = edh.EmployeeID

INNER JOIN AdventureWorks.HumanResources.Department d

ON edh.DepartmentID = d.DepartmentID

INNER JOIN AdventureWorks.Person.Contact c

ON e.ContactID = c.ContactID

WHERE edh.EndDate is NULL

-- SELECT * FROM Employee

-- SQL table-valued function - user-defined function UDF - Tree processing function

CREATE FUNCTION fnOrgChartSubTree(@EmployeeID AS INT)

RETURNS @TREE TABLE (

EmployeeID INT

,EmployeeName VARCHAR(35)

,Department VARCHAR(30)

,ManagerID INT

,ManagerName VARCHAR(35)

,OrgChartLevel INT )

BEGIN

-- SQL common table expression - CTE - SQL recursive CTE

WITH cteOrgChartSubTree(EmployeeID, EmployeeName, Department,

ManagerID, ManagerName, OrgChartLevel)

(

-- Anchor (root) node

SELECT e1.EmployeeID, e1.StaffName, e1.Department,

e1.ManagerID, e2.StaffName, 0

FROM Employee e1

LEFT JOIN Employee e2

ON (e1.ManagerID = e2.EmployeeID)

WHERE e1.EmployeeID = @EmployeeID

UNION ALL

-- Recursive nodes to leaf level

SELECT e1.EmployeeID, e1.StaffName, e1.Department,

e1.ManagerID, e2.StaffName, cte.OrgChartLevel+1

FROM Employee e1

INNER JOIN Employee e2

ON e1.ManagerID = e2.EmployeeID

JOIN cteOrgChartSubTree AS cte

ON e1.ManagerID = cte.EmployeeID

)

-- Return results

INSERT INTO @TREE

SELECT * FROM cteOrgChartSubTree;

RETURN

END

-- Find CEO

SELECT CEO=StaffName, CEOid =EmployeeID

FROM Employee

WHERE ManagerID is NULL

/* Results

CEO CEOid

Sánchez, Ken 109

-- Test UDF - user-defined function

-- Generate orgchart starting with CEO

SELECT EmpID = EmployeeID, EmpName=EmployeeName, Department,

MgrID=ManagerID, MgrName=ManagerName, OCLvl=OrgChartLevel

FROM dbo.fnOrgChartSubTree(109)

/* 290 rows - Partial results

EmpID EmpName Department MgrID MgrName OCLvl

109 Sánchez, Ken Executive NULL NULL 0

6 Bradley, David Marketing 109 Sánchez, Ken 1

12 Duffy, Terri Engineering 109 Sánchez, Ken 1

42 Trenary, Jean Informa… 109 Sánchez, Ken 1

140 Norman, Laura Executive 109 Sánchez, Ken 1

148 Hamilton, James Production 109 Sánchez, Ken 1

273 Welcker, Brian Sales 109 Sánchez, Ken 1

268 Jiang, Stephen Sales 273 Welcker, Brian 2

284 Alberts, Amy Sales 273 Welcker, Brian 2

-- Test UDF for supervisor Miller, Dylan

SELECT EmpID = EmployeeID, EmpName=EmployeeName, Department,

MgrID=ManagerID, MgrName=ManagerName, OCLvl=OrgChartLevel

FROM dbo.fnOrgChartSubTree(158)

/* Results

R & D = Research and Development

EmpID EmpName Department MgrID MgrName OCLvl

158 Miller, Dylan R & D 3 Tamburello… 0

79 Margheim, Diane R & D 158 Miller, Dylan 1

114 Matthew, Gigi R & D 158 Miller, Dylan 1

217 Raheem, Michael R & D 158 Miller, Dylan 1

-- Test UDF for staff Margheim, Diane (leaf level on orgchart tree)

SELECT EmpID = EmployeeID, EmpName=EmployeeName, Department,

MgrID=ManagerID, MgrName=ManagerName, OCLvl=OrgChartLevel

FROM dbo.fnOrgChartSubTree(79)

/* Results

EmpID EmpName Department MgrID MgrName OCLvl

79 Margheim, Diane Research and... 158 Miller, Dylan 0

-- Get top-level executives - Level 1 when root is CEO

-- SQL select into create temporary table - SQL cross apply

SELECT ExecName=oc.EmployeeName, oc.EmployeeID, oc.Department

INTO #EXECS

FROM Employee e

CROSS APPLY dbo.fnOrgChartSubTree(e.EmployeeID) AS oc

WHERE e.EmployeeID = 109

and oc.OrgChartLevel=1;

SELECT * FROM #EXECS

/* Results

ExecName EmployeeID Department

Bradley, David 6 Marketing

Duffy, Terri 12 Engineering

Trenary, Jean 42 Information Services

Norman, Laura 140 Executive

Hamilton, James 148 Production

Welcker, Brian 273 Sales

-- Get orgchart by executives

-- Note: AdventureWorks database does not have Department Manager info

-- SQL cross apply - SQL IN operator

SELECT Executive=e.StaffName, e.Title, oc.Department,

Staff=oc.EmployeeName, Supervisor = oc.ManagerName

FROM Employee e

CROSS APPLY dbo.fnOrgChartSubTree(e.EmployeeID) AS oc

WHERE e.EmployeeID IN (Select EmployeeID FROM #EXECS)

ORDER by Executive, Department, Supervisor, Staff

/* 289 rows CEO Ken Sanchez not included - Partial results

VPS = Vice President of Sales

WB = Welcker, Brian

Dept = Department

Executive Title Dept Staff Supervisor

WB VPS Sales Tsoflias, Lynn Abbas, Syed

WB VPS Sales Pak, Jae Alberts, Amy

WB VPS Sales Valdez, Rachel Alberts, Amy

WB VPS Sales Varkey C.., Ranjit Alberts, Amy

WB VPS Sales Ansman-W.., Pamela Jiang, Stephen

WB VPS Sales Blythe, Michael Jiang, Stephen

WB VPS Sales Campbell, David Jiang, Stephen

WB VPS Sales Carson, Jillian Jiang, Stephen

WB VPS Sales Ito, Shu Jiang, Stephen

WB VPS Sales Mensa-A.., Tete Jiang, Stephen

WB VPS Sales Mitchell, Linda Jiang, Stephen

WB VPS Sales Reiter, Tsvi Jiang, Stephen

WB VPS Sales Saraiva, José Jiang, Stephen

WB VPS Sales Vargas, Garrett Jiang, Stephen

WB VPS Sales Welcker, Brian Sánchez, Ken

WB VPS Sales Abbas, Syed Welcker, Brian

WB VPS Sales Alberts, Amy Welcker, Brian

WB VPS Sales Jiang, Stephen Welcker, Brian

-- Cleanup

DROP TABLE tempdb.dbo.Employee

DROP TABLE #EXECS

In the following sql CROSS APPLY example, first we create a function (UDF) to get the total sales for a bike store. Second, we use CROSS APPLY to get the sales figures for the early dealers (CustomerID < 100) of AdventureWorks Cycles. Naturally, we can achieve the same results in a single complex query which may even be faster. The CROSS APPLY advantage appears when the user-defined function is used in several queries: developer productivity gain.

-- SQL create function

USE AdventureWorks;

CREATE FUNCTION dbo.fnGetTotalSalesByCustomer(@CustID int)

RETURNS TABLE

RETURN

SELECT Store = s.Name,

TotalSales = '$'+convert(varchar,TotalSales,1)

FROM

(

SELECT CustomerID = @CustID, TotalSales=sum(SubTotal)

FROM Sales.SalesOrderHeader

WHERE CustomerID =@CustID

) soh

INNER JOIN Sales.Store s

ON soh.CustomerID = s.CustomerID

-- SQL cross apply use - SQL Server cross apply

SELECT Store, TotalSales

FROM Sales.Customer AS c

CROSS APPLY dbo.fnGetTotalSalesByCustomer (c.CustomerID) tsc

WHERE c.CustomerID < 100

ORDER BY Store

/* Partial results

Store TotalSales

A Bike Store $102,351.80

Advanced Bike Components $433,942.38

Aerobic Exercise Company $3,301.21

Associated Bikes $9,384.45

Bicycle Exporters $37,684.82

Bicycle Warehouse Inc. $7,959.01

Bike World $112,601.32

In the following example we calculate financial statistics using Aggregate Functions for each dealer which sells AdventureWorks mountain bikes and associated products.
Here is the listing:

USE tempdb

-- drop FUNCTION dbo.fnOrderFingerprint

CREATE FUNCTION dbo.fnOrderFingerprint

(@CustomerID AS INT)

RETURNS TABLE

RETURN

SELECT Label = 'Maximum $',

TotalDue = max(TotalDue)

FROM AdventureWorks.Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID

UNION

SELECT Label = 'Average $',

TotalDue = avg(TotalDue)

FROM AdventureWorks.Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID

UNION

SELECT Label = 'Minimum $',

TotalDue = min(TotalDue)

FROM AdventureWorks.Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID

UNION

SELECT Label = 'Order Count',

TotalDue = count(TotalDue)

FROM AdventureWorks.Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID

UNION

SELECT Label = 'Standard Deviation $',

TotalDue = stdev(TotalDue)

FROM AdventureWorks.Sales.SalesOrderHeader

WHERE CustomerID = @CustomerID

SELECT Customer = S.Name,

F.Label,

[Total Due] = left(convert(VARCHAR,convert(MONEY,F.TotalDue),1),

len(convert(VARCHAR,convert(MONEY,F.TotalDue),1)) - 3)

FROM AdventureWorks.Sales.Store AS S

JOIN AdventureWorks.Sales.Customer AS C

ON S.CustomerID = C.CustomerID

CROSS APPLY tempdb.dbo.fnOrderFingerprint(C.CustomerID) AS F

ORDER BY Customer ASC,

Label DESC

GO
This is the partial report:

Customer	Label	TotalDue
Excellent Riding Supplies	Standard Deviation $	34,246
Excellent Riding Supplies	Order Count	12
Excellent Riding Supplies	Minimum $	34,102
Excellent Riding Supplies	Maximum $	150,167
Excellent Riding Supplies	Average $	94,562
Exceptional Cycle Services	Standard Deviation $	3,978
Exceptional Cycle Services	Order Count	4
Exceptional Cycle Services	Minimum $	4,012
Exceptional Cycle Services	Maximum $	13,016
Exceptional Cycle Services	Average $	7,913
Exchange Parts Inc.	Standard Deviation $	3,965
Exchange Parts Inc.	Order Count	4
Exchange Parts Inc.	Minimum $	34,610
Exchange Parts Inc.	Maximum $	43,277
Exchange Parts Inc.	Average $	38,982
Exclusive Bicycle Mart	Standard Deviation $	576
Exclusive Bicycle Mart	Order Count	6
Exclusive Bicycle Mart	Minimum $	429
Exclusive Bicycle Mart	Maximum $	1,943
Exclusive Bicycle Mart	Average $	1,035

Source : sqlusa

Ada Lovelace

Tuesday, January 27, 2015

All the Fuss about CROSS APPLY .

No comments:

Post a Comment