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.
Adventure Works 2008 Database:
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)
GO
;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
------------
-- 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]
GO
/* 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;
GO
SET NOCOUNT ON;
CREATE TABLE Account (
AccountID INT IDENTITY PRIMARY KEY,
AccountName VARCHAR(50),
CreateDate SMALLDATETIME DEFAULT (CURRENT_TIMESTAMP),
IsActive BIT DEFAULT (1))
GO
CREATE TABLE Deposit (
DepositId INT IDENTITY PRIMARY KEY,
AccountID INT,
Amount MONEY,
DepositDate SMALLDATETIME DEFAULT (getdate()))
GO
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)
GO
-- 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;
GO
-- SQL inline function
-- User-defined inline function
CREATE FUNCTION Sales.fnTopNOrders (
@CustomerID AS INT,
@n AS INT )
RETURNS TABLE
AS
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
GO
-- 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
GO
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
GO
/* 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 sql
GO
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;
GO
-- 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
GO
-- 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 )
AS
BEGIN
-- SQL common table expression - CTE - SQL recursive CTE
WITH cteOrgChartSubTree(EmployeeID, EmployeeName, Department,
ManagerID, ManagerName, OrgChartLevel)
AS
(
-- 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
GO
-- Find CEO
SELECT CEO=StaffName, CEOid =EmployeeID
FROM Employee
WHERE ManagerID is NULL
GO
/* 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)
GO
/* 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)
GO
/* 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)
GO
/* 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
GO
/* 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
GO
/* 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
GO
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;
GO
CREATE FUNCTION dbo.fnGetTotalSalesByCustomer(@CustID int)
RETURNS TABLE
AS
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
GO
-- 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
GO
/* 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
GO
-- drop FUNCTION dbo.fnOrderFingerprint
CREATE FUNCTION dbo.fnOrderFingerprint
(@CustomerID AS INT)
RETURNS TABLE
AS
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
GO
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
Adventure Works 2008 Database:
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)
GO
;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
------------
-- 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]
GO
/* 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;
GO
SET NOCOUNT ON;
CREATE TABLE Account (
AccountID INT IDENTITY PRIMARY KEY,
AccountName VARCHAR(50),
CreateDate SMALLDATETIME DEFAULT (CURRENT_TIMESTAMP),
IsActive BIT DEFAULT (1))
GO
CREATE TABLE Deposit (
DepositId INT IDENTITY PRIMARY KEY,
AccountID INT,
Amount MONEY,
DepositDate SMALLDATETIME DEFAULT (getdate()))
GO
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)
GO
-- 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;
GO
-- SQL inline function
-- User-defined inline function
CREATE FUNCTION Sales.fnTopNOrders (
@CustomerID AS INT,
@n AS INT )
RETURNS TABLE
AS
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
GO
-- 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
GO
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
GO
/* 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 sql
GO
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;
GO
-- 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
GO
-- 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 )
AS
BEGIN
-- SQL common table expression - CTE - SQL recursive CTE
WITH cteOrgChartSubTree(EmployeeID, EmployeeName, Department,
ManagerID, ManagerName, OrgChartLevel)
AS
(
-- 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
GO
-- Find CEO
SELECT CEO=StaffName, CEOid =EmployeeID
FROM Employee
WHERE ManagerID is NULL
GO
/* 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)
GO
/* 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)
GO
/* 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)
GO
/* 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
GO
/* 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
GO
/* 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
GO
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;
GO
CREATE FUNCTION dbo.fnGetTotalSalesByCustomer(@CustID int)
RETURNS TABLE
AS
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
GO
-- 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
GO
/* 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
GO
-- drop FUNCTION dbo.fnOrderFingerprint
CREATE FUNCTION dbo.fnOrderFingerprint
(@CustomerID AS INT)
RETURNS TABLE
AS
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
GO
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
No comments:
Post a Comment