June 10, 2016 at 11:31 am
simon.barnes (6/10/2016)
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.
Yes, I do much the same when reference an actual table or view.
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.
LOL! In any case, the point I'm making is to use CROSS APPLY to create a derived column without modifying the DDL. Just as the schema-defined derived columns don't need their own alias, neither do those created on-the-fly in this manner. If it makes you uncomfortable (though the hangover might do that anyway!) just wrap the SELECT + CROSS apply in an aliased sub query. Done and done!
BTW, you might consider drinking a little less...
Gerald Britton, Pluralsight courses
June 10, 2016 at 11:59 am
g.britton (6/10/2016)
LOL! In any case, the point I'm making is to use CROSS APPLY to create a derived column without modifying the DDL. Just as the schema-defined derived columns don't need their own alias, neither do those created on-the-fly in this manner. If it makes you uncomfortable (though the hangover might do that anyway!) just wrap the SELECT + CROSS apply in an aliased sub query. Done and done!BTW, you might consider drinking a little less...
But I do all my best databasing when I'm drunk! 😀
Yeah, the qualification insistence just comes from one too many times seeing a query suddenly start failing because a column with the same name as a column or alias has been added to one of the other objects in the query and suddenly it's ambiguous. The underscore I actually quite liked; if I'm throwing example code over to someone and it uses variables I always make sure to name one of them [font="Courier New"]@[/font] just to hear the "…weh?!" drift over from their desk a few seconds later.
June 10, 2016 at 12:22 pm
simon.barnes (6/10/2016)
g.britton (6/10/2016)
LOL! In any case, the point I'm making is to use CROSS APPLY to create a derived column without modifying the DDL. Just as the schema-defined derived columns don't need their own alias, neither do those created on-the-fly in this manner. If it makes you uncomfortable (though the hangover might do that anyway!) just wrap the SELECT + CROSS apply in an aliased sub query. Done and done!BTW, you might consider drinking a little less...
But I do all my best databasing when I'm drunk! 😀
Yeah, the qualification insistence just comes from one too many times seeing a query suddenly start failing because a column with the same name as a column or alias has been added to one of the other objects in the query and suddenly it's ambiguous. The underscore I actually quite liked; if I'm throwing example code over to someone and it uses variables I always make sure to name one of them [font="Courier New"]@[/font] just to hear the "…weh?!" drift over from their desk a few seconds later.
FWIW I grabbed the underscore idea from years of programming in Python. It's the typical "throwaway" identifier. Almost, but not quite, anonymous. (like "@" in SQL)
Gerald Britton, Pluralsight courses
June 10, 2016 at 12:29 pm
g.britton (6/10/2016)
FWIW I grabbed the underscore idea from years of programming in Python. It's the typical "throwaway" identifier. Almost, but not quite, anonymous. (like "@" in SQL)
Ah yes, I've seen that occasionally. I'm just awful for single-letter variable names in Python. And one-liners that I'll spend thirty minutes unravelling when I come across my own code six months later.
June 10, 2016 at 1:37 pm
Great idea. I'll definitely use this in the future.
June 11, 2016 at 2:10 am
Good article.
I use cross apply quiete a lot in DWH.
When need to build template for the report and then populate it with actual data.
Building dimensions with cross apply and numbers and calendar table.
June 13, 2016 at 9:25 am
I vote for the CTE/subquery approach.
The day you're porting your SQL to another database engine, e.g. ORACLE, you'll damn all of the MSSQL-specific code 🙂
June 14, 2016 at 5:53 pm
INCREDIBLEmouse (4/2/2015)
Nice article. Good points. And all that Jazz.But - I was stunned by the use of underscores as source aliases.
cross apply (something) as _
cross apply (somethingelse) as _1
That threw me for a loop, since it's using non-alphanumeric chars as a name, and makes for cryptic select statement references.
Why not, cross apply (something) as SomethingDescriptiveButNotThisLong
Or am I missing something obvious?
I've been using "info" as a standard for CROSS APPLY/OUTER APPLY aliases. It saves me the effort of having to think of an alias 😀
CROSS APPLY (something) info
CROSS APPLY (something else) info2
Sean
June 15, 2016 at 10:56 am
Great Article.
Used it today.
This will make a big difference in maintenance. Thank-you.
June 16, 2016 at 9:36 am
obouda (6/13/2016)
I vote for the CTE/subquery approach.The day you're porting your SQL to another database engine, e.g. ORACLE, you'll damn all of the MSSQL-specific code 🙂
I have a feeling that one cuts both ways
Gerald Britton, Pluralsight courses
June 16, 2016 at 11:52 pm
g.britton (6/16/2016)
obouda (6/13/2016)
I vote for the CTE/subquery approach.The day you're porting your SQL to another database engine, e.g. ORACLE, you'll damn all of the MSSQL-specific code 🙂
I have a feeling that one cuts both ways
To be clear:
Both CTE and subqeury work in MSSQL, ORACLE, SAP HANA, Firebird and Derby.
I don't have experience with other engines, but I suppose it should work everywhere.
Contrary, CROSS APPLY does NOT work in ORACLE and SAP HANA (this syntax is not supported).
Regarding Derby and Firebird, I think that they do not support it too, but I'm not 100% sure.
I loved CROSS/OUTER APPLY till I was developing for MSSQL only.
But now I'm involved in development targeting the mentioned engines, and I prefer the notation which works everywhere.
Just my 2 cents 🙂
June 17, 2016 at 4:35 am
obouda (6/16/2016)
g.britton (6/16/2016)
obouda (6/13/2016)
I vote for the CTE/subquery approach.The day you're porting your SQL to another database engine, e.g. ORACLE, you'll damn all of the MSSQL-specific code 🙂
I have a feeling that one cuts both ways
To be clear:
Both CTE and subqeury work in MSSQL, ORACLE, SAP HANA, Firebird and Derby.
I don't have experience with other engines, but I suppose it should work everywhere.
Contrary, CROSS APPLY does NOT work in ORACLE and SAP HANA (this syntax is not supported).
Regarding Derby and Firebird, I think that they do not support it too, but I'm not 100% sure.
I loved CROSS/OUTER APPLY till I was developing for MSSQL only.
But now I'm involved in development targeting the mentioned engines, and I prefer the notation which works everywhere.
Just my 2 cents 🙂
Fair enough. However you will often wind up with more repeated code -- not as DRY -- which can lead to higher maintenance costs.
Also, to be clear, there are highly useful features in many SQL versions that are not supported by others. We use them nonetheless
Gerald Britton, Pluralsight courses
June 17, 2016 at 5:07 am
g.britton (6/17/2016)
Also, to be clear, there are highly useful features in many SQL versions that are not supported by others. We use them nonetheless
We use them too - but only when it solves a severe performance issue.
Because it's DRYer to have one code for all platforms than to maintain several versions of each query.
I just wanted to point out that "what is DRYer" may depend on other factors also.
Everyone developing SQL code should keep in mind that the management may come tomorrow with a decision like "we're supporting ORACLE also since the next release". And such decision may turn all of the "extra DRY" code into a nightmare.
Therefore, my recommendation is:
Use CROSS APPLY only if you're sure you will never need to support another SQL engine.
The overhead of using CTE/subquery instead is negligible (at least in this use case).
June 17, 2016 at 9:24 am
obouda (6/17/2016)
g.britton (6/17/2016)
Also, to be clear, there are highly useful features in many SQL versions that are not supported by others. We use them nonetheless
We use them too - but only when it solves a severe performance issue.
Because it's DRYer to have one code for all platforms than to maintain several versions of each query.
That would certainly be true if you need to support the same query on multiple platforms. That is not my experience. Also, keep in mind that this forum is SQL Server Central. It is not the best place to talk polyglot SQL, or ANSI SQL (which no one fully supports, I think) for that matter.
I just wanted to point out that "what is DRYer" may depend on other factors also.
Everyone developing SQL code should keep in mind that the management may come tomorrow with a decision like "we're supporting ORACLE also since the next release". And such decision may turn all of the "extra DRY" code into a nightmare.
Well, management may come in and say, "we're going pure Hadoop tomorrow. All you SQL guys, you're fired unless you can learn Java overnight!" I have never, ever developed code based on what "might" happen. Unless there's solid evidence to suggest some change in direction, I will most certainly not clutter my mind with it.
Therefore, my recommendation is:
Use CROSS APPLY only if you're sure you will never need to support another SQL engine.
The overhead of using CTE/subquery instead is negligible (at least in this use case).
Replacing a CROSS APPLY, as used in this article, with a CTE that does the same thing will add no overhead whatsoever. (Well, TBH there may be some difference in compile time, but that is not something I usually worry much about.)
My general recommendation that you exploit the features of the SQL engines you currently have (we run SQL Server, Oracle, DB2, MySql and probably others) and not spend much time worrying about "what if?"
FWIW I think it should be fairly straightforward to machine-translate in-row CROSS APPLY's to CTEs and vice versa. Once you parse the code (you could use the parser from any open source SQL engine), you can emit whatever code you like.
Gerald Britton, Pluralsight courses
June 20, 2016 at 2:33 am
To be clear:
Both CTE and subqeury work in MSSQL, ORACLE, SAP HANA, Firebird and Derby.
I don't have experience with other engines, but I suppose it should work everywhere.
ORACLE still hasn't implemented CTE in MySQL (v.5.7).
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply