Some years ago, I was reading one book of Itzik Ben-Gan where he explained in depth the use of a new operator: APPLY, I told myself, it just another new feature more. I didn’t understand the relevance and the many versatile applications it had, through time, I have been using APPLY in many scenarios. Saving time and resources it is a fundamental part of each TSQL development, in this short article I would like to explain a simple trick with APPLY operator.
The requirement can sound exotic but is very illustrative, I am using the new example DB for SQL Server 2016 (World Wide Imports), the link to download is the next: https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers.
Suppose that you have received a new requirement which was raised by Supply Chain Department; they want to know the total of Orders for the first day of each year, obviously, we need a dynamic way to do it, I will show two different approaches to get it.
Approach number 1: Using CTE
The use of CTE can be useful to let us access and get the first day of each year.
WITH CTE_1 ( OrderYear ) AS ( SELECT DISTINCT(YEAR(OrderDate)) FROM WideWorldImporters.[Sales].[Orders] ), CTE_2(Dates) AS ( SELECT DATEFROMPARTS(OrderYear, 1, 1) FROM CTE_1 ) SELECT O.OrderID,O.CustomerID,O.SalespersonPersonID,O.OrderDate FROM WideWorldImporters.[Sales].[Orders] AS O WHERE EXISTS (SELECT 1 FROM CTE_2 WHERE O.OrderDate = CTE_2.Dates);
After to run this query, I would like to show you the Execution Plan, for this article I am using SQL Sentry Plan Explorer tool for analyzing Execution Plans.
The first query uses Distinct statement, this raise a clustered index scan, however, the number of operators required are many, but I am mainly concerned about the message in this operator:
You can see a Residual IO and a warning for the operator, to know more information about Residual IO problem you can get here: https://sqlperformance.com/2016/06/sql-indexes/actual-rows-read-warnings-plan-explorer.
Approach number 2: Using CROSS APPLY
The operator CROSS APPLY can help a lot in this scenario, inside APPLY operator we can incorporate VALUES sentence, this allow us to take information from outside or input table, in this solution we are taking the OrderDate from Orders table and getting the YEAR, on the other hand, I adding a second APPLY to return a date which is built from year has got in previous operation, the DATEFROMPARTS is an interesting operator that allow us to get a valid date from input values for year, month and day.
SELECT O.OrderID,O.CustomerID,O.SalespersonPersonID,O.OrderDate FROM WideWorldImporters.[Sales].[Orders] AS O CROSS APPLY ( VALUES(YEAR(O.OrderDate))) AS A1(orderyear) CROSS APPLY ( VALUES( DATEFROMPARTS(orderyear,1,1))) AS A2(beginingyear) WHERE O.OrderDate IN (A2.beginingyear);
Is extremely important to note that warning message has vanished and the amount of operator has reduced dramatically. You can observe the combination of APPLY and VALUES, I would like to recommend you to research other interesting application of this technique, for example, Unpivoting with APPLY. Well, I hope this small trick will be useful for you. Happy codings!!!!