May 26, 2015 at 5:43 pm
Nice article. I use APPLY quite a bit...but have never even thought to use it in this scenario. You've opened my eyes where I didn't think you could or would...very nice. 😀
June 9, 2016 at 11:22 pm
HOW about Common Table Expression expressions? You should be able to use them in View definition 🙂 And I believe that they performed are BETTER than CROSS APPLY
June 10, 2016 at 3:53 am
Thanks for good article!
Haven't seen "_(dt_year)" before?
How is the underscore here working?
SELECT dt, dt_year
FROM (VALUES (GetDate())) cur(dt)
CROSS APPLY (SELECT YEAR(cur.dt)) _(dt_year)
June 10, 2016 at 4:02 am
tom.gjos (6/10/2016)
Thanks for good article!Haven't seen "_(dt_year)" before?
How is the underscore here working?
SELECT dt, dt_year
FROM (VALUES (GetDate())) cur(dt)
CROSS APPLY (SELECT YEAR(cur.dt)) _(dt_year)
It's just an alias for the virtual table [font="Courier New"](VALUES (GetDate())) cur(dt) CROSS APPLY (SELECT YEAR(cur.dt))[/font]. It wouldn't work in my environment, because I have a rule that every column name has to be qualified with a table alias - that would make it [font="Courier New"]_.dt_year[/font] in this case. Otherwise, if you introduce another table or view to your query that happens to have a column called dt_year, you get an ambiguous column error.
John
June 10, 2016 at 6:06 am
The use of APPLY is interesting and I did learn a couple of things, but in my opinion the CTE approach exemplified earlier is cleaner and easier to read especially after moving the selection criteria to the CTE:
WITH CTE AS
(
SELECT [Model]
, [CalendarYear]
, [Month]
, [Amount]
, [Quantity]
, 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
FROM [dbo].[vDMPrep]
WHERE
[Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
'Road-650', 'Road-750', 'Touring-1000')
)
SELECT
[ModelRegion]
,[TimeIndex]
,Sum([Quantity]) AS [Quantity]
,Sum([Amount]) AS [Amount]
,[CalendarYear]
,[Month]
,[ReportingDate]
FROM CTE
GROUP BY
[ModelRegion]
,[TimeIndex]
,[CalendarYear]
,[Month]
,[ReportingDate]
With this approach, the CTE performs data collection and transformation leaving the outer query to simply perform aggregation.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
June 10, 2016 at 6:49 am
fregatepllada (6/9/2016)
HOW about Common Table Expression expressions? You should be able to use them in View definition 🙂 And I believe that they performed are BETTER than CROSS APPLY
As far as performance goes, the only true answer is "it depends." However for the example in this article, using CROSS APPLY in this way has no performance penalty at all.
BTW, always keep in mind that CTEs are just another way to write sub queries, and generally result in similar -- if not identical -- execution plans as the same queries written with sub queries instead (except for recursive CTEs that is).
OTOH I love CTEs for their ability to clean up a rats' nest of sub queries. To my eye at least, writing what are effectively sub queries up front yields easier-to-read code.
Gerald Britton, Pluralsight courses
June 10, 2016 at 7:19 am
Aaron N. Cutshall (6/10/2016)
The use of APPLY is interesting and I did learn a couple of things, but in my opinion the CTE approach exemplified earlier is cleaner and easier to read especially after moving the selection criteria to the CTE:
WITH CTE AS
(
SELECT [Model]
, [CalendarYear]
, [Month]
, [Amount]
, [Quantity]
, 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
FROM [dbo].[vDMPrep]
WHERE
[Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
'Road-650', 'Road-750', 'Touring-1000')
)
SELECT
[ModelRegion]
,[TimeIndex]
,Sum([Quantity]) AS [Quantity]
,Sum([Amount]) AS [Amount]
,[CalendarYear]
,[Month]
,[ReportingDate]
FROM CTE
GROUP BY
[ModelRegion]
,[TimeIndex]
,[CalendarYear]
,[Month]
,[ReportingDate]
With this approach, the CTE performs data collection and transformation leaving the outer query to simply perform aggregation.
Yes, that is an attractive, alternative approach. In the spirit of DRY, I feel I should point out that this variant repeats references to
[CalendarYear]
[Month]
[Amount]
[Quantity]
that occur only once in the CROSS APPLY version. That means that if a new column is added (or an existing column removed), there are two places to change, not one. For me at least, that would potentially increase future maintenance efforts. Let's remember that code, whatever the language, is written (that is, created) once but often modified many times over by many different people.
Gerald Britton, Pluralsight courses
June 10, 2016 at 7:30 am
g.britton (6/10/2016)
In the spirit of DRY, I feel I should point out that this variant repeats references
Yes, you are correct that column references are repeated. I feel that those are safer to repeat than calculated or derived columns for clarity's sake, but your point is taken. Still, I'd take clarity over cleverness any day provided performance and results are not impacted.
LinkedIn: https://www.linkedin.com/in/sqlrv
Website: https://www.sqlrv.com
June 10, 2016 at 7:43 am
Aaron N. Cutshall (6/10/2016) [snip] Still, I'd take clarity over cleverness any day provided performance and results are not impacted.
As do I! Though I don't consider the use of CROSS APPLY any more clever than a CTE performing the same function. It's just an alternate (and DRYer!) approach.
Of course, this is a (very) simple example. Imagine there were many, many more columns. As the number of columns goes up, so does the repeated code and the choice between which tool to use becomes easier.
Gerald Britton, Pluralsight courses
June 10, 2016 at 7:54 am
What about using the APPLY in the CTE?
June 10, 2016 at 8:00 am
John Hick-456673 (6/10/2016)
What about using the APPLY in the CTE?
If it leads to DRYer, more maintainable code, count me in! Why not take the example and rework it as you suggest then post your results?
Gerald Britton, Pluralsight courses
June 10, 2016 at 9:35 am
Oh wow. Why have I never considered using APPLY for this purpose? You've just simplified my life. Thank you, sir!
June 10, 2016 at 9:56 am
Nice article, but
- minus point for the usage of [dbo].[udfBuildISO8601Date] (UDFs are slow); there is a built in function DATEFROMPARTS() with the same syntax, that outputs a date. If necessary you could convert this date to whatever you want
- instead of CROSS APPLY you could have used CROSS JOIN too
God is real, unless declared integer.
June 10, 2016 at 11:06 am
ronmoses (4/2/2015)
INCREDIBLEmouse (4/2/2015)
Why not, cross apply (something) as SomethingDescriptiveButNotThisLongI also prefer at least semi-descriptive table aliases, just to help me keep track of where different values are coming from. So for a table of patient history data, I'll typically use 'pthist', and so on.
Not to mention that if anybody tries to slip code containing an unqualified column reference past me, they'll get their login revoked for a number of minutes proportional to how hungover I am.
Nice article though, I've actually used this before in some quite horrifically complex temporal search procedures and functions. When you're mangling datetime data types to the point that you need 10+ lines with indents just to make the code for a single expression readable, you don't want to have to paste those lines all over the place and then have to either comment them with their column alias or go hunting around the SELECT to find out what the hell they represent later on
June 10, 2016 at 11:24 am
t.franz (6/10/2016)
Nice article, but- minus point for the usage of [dbo].[udfBuildISO8601Date] (UDFs are slow); there is a built in function DATEFROMPARTS() with the same syntax, that outputs a date. If necessary you could convert this date to whatever you want
The point of the article was to redo a view in AdventureWorks to use CROSS APPLY, not to optimize it. Besides DATEFROMPARTS arrived with SQL Server 2012, and the AdventureWorksDW20008R2 database is designed for SQL Server 2008 R2, where that function is not available.
instead of CROSS APPLY you could have used CROSS JOIN too
Not sure how you could do that. You'd need to re-reference the source data set, and then how would you match the rows to get the correct derived column? You could maybe use an INNER JOIN (assuming you get the join columns correct) but I can't see how a CROSS JOIN would work.
Still, if you're up for it, rewrite the solution using CROSS JOIN instead of CROSS APPLY and post your results. I for one would be most interested!
Gerald Britton, Pluralsight courses
Viewing 15 posts - 31 through 45 (of 59 total)
You must be logged in to reply to this topic. Login to reply