Wouldn't the logic of the APPLY syntax be logically similar to the following:
,Sum(T.[Quantity]) AS [Quantity]
,Sum(T.[Amount]) AS [Amount]
I don't have an AdventureWorks db handy
Grab it here: http://msftdbprodsamples.codeplex.com/
I keep it in my test DB since so many Microsoft examples use it as a base.
to compare the execution plans but I'd be most interested to know what are the pro's and con's between the APPLY and the above query, especially in terms of performance. Would there be more overhead in my query as compared to using APPLY?
The execution plans are virtually identical. In fact, the refactored execution plans have one less "Compute Scalar" step than the original.
PS: Very sorry that the indentation doesn't seem to work here!
Easy to do: just wrap your SQL in the "code" tags provided.
FWIW here is a third approach using a CTE. Note however, that with both the subquery and the CTE approach, you need to repeat some columns (Month, CalendarYear, Model (Subquery approach))
WITH CTE AS
, CASE [Model]
WHEN 'Mountain-100' THEN 'M200'
WHEN 'Road-150' THEN 'R250'
WHEN 'Road-650' THEN 'R750'
WHEN 'Touring-1000' THEN 'T1000'
ELSE Left([Model], 1) + Right([Model], 3)
END + ' ' + [Region] AS [ModelRegion]
, (Convert(Integer, [CalendarYear]) * 100) + Convert(Integer, [Month]) AS [TimeIndex]
, [dbo].[udfBuildISO8601Date] ([CalendarYear], [Month], 25) AS ReportingDate
,Sum([Quantity]) AS [Quantity]
,Sum([Amount]) AS [Amount]
[Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
'Road-650', 'Road-750', 'Touring-1000')