|
The following
SQL Server T-SQL example scripts demonstrate how CROSS APPLY can be used to "join" a table
and a table-valued function:
USE AdventureWorks2008;
-- sys.dm_exec_sql_text is a table-valued function (TFV) defined in master db - DMF
-- sys.dm_exec_query_stats is a Dynamic Management View - DMV
-- CROSS APPLY invokes the TVF for each row returned from the view SELECT query
SELECT DBID = st.dbid,
Query = LEFT(st.text, 30),
ExecCount = execution_count,
WorkerTime = total_worker_time,
PhysReads = total_physical_reads,
LogiReads = total_logical_reads
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY ExecCount desc;
/* DBID Query ExecCount WorkerTime PhysReads
....
4 CREATE PROCEDURE sp_jobhistory 4 1000 0
4 CREATE PROCEDURE sp_jobhistory 4 0 1 ......*/
------------
USE AdventureWorks;
GO
-- SQL Server user-defined function - UDF - Table-valued function - inline TVF
CREATE FUNCTION Sales.fnTopNOrders
(@CustomerID AS INT,
@n AS INT)
RETURNS TABLE
AS
RETURN
SELECT TOP ( @n ) SalesOrderID,
ShipDate = convert(CHAR(10),ShipDate,112),
TotalDue = convert(VARCHAR,TotalDue,1)
FROM AdventureWorks.Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
ORDER BY TotalDue DESC
GO
-- SQL join table-valued function - SQL use function to join table
-- SQL cross apply - SQL join table & table-valued function
SELECT StoreName = s.Name,
[Top].ShipDate,
[Top].SalesOrderID,
-- SQL currency format – format money
TotalDue = '$' + [Top].TotalDue
FROM AdventureWorks.Sales.Store AS s
INNER 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 results
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
*/
------------
-- SQL Server DMV with CROSS APPLY - sys.dm_exec_cached_plans Dynamic Management View
-- Get cached plans information - sys.dm_exec_sql_text Dynamic Management Function
SELECT cp.*,
sqltext.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) AS sqltext
------------
Related Link:
All the Fuss about CROSS APPLY
|
