April 2, 2015 at 10:22 am
Never knew this--thanks for the article!
April 2, 2015 at 10:28 am
INCREDIBLEmouse (4/2/2015)
Why not, cross apply (something) as SomethingDescriptiveButNotThisLong
I 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.
In this case, since it's a structure I'm using solely for a cross-apply, I guess I'd abbreviate that to help me where it came from. So I'd probably use 'crap'.
Oh wait.:ermm:
long way to go for a dumb joke,
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
April 2, 2015 at 10:44 am
Nice article. Learned something really useful (and powerful) today. Execution plans and page reads are identical, yet it is much easier to read and maintain now. Thank you.
April 2, 2015 at 11:00 am
I've never seen that particular SQL coding before and I will try to use that. I have seen tons of SP's and views here where I work with lots of repeating columns etc... exactly as described. THis leads me to believe that not a lot of folks out there are too familiar with it. THe only issue I can see is that sometimes to make things DRYer (hence more manageable/maintainable), the coding can get a little complicated due to the use of TSQL that not many are familiar with, so while it may improve coding in an effort to make more readable and manageable, for some folks, it may actually make things a little more complicated because they may not understand what is going on, which will then require some research and learning before they do understand and can make a change that will not adversely affect the functionality of the view or SP... Now this is fine generally, but sometimes when you are under the gun to implement a fix or change, having to learn a bunch of new stuff and then implement a change in that type of environment can be very stressful!!!
Personally, I like this and I will use it, but we need to make sure that others we are working with also understand new functionality/coding techniques as well before it truly does make maintenance/manageability easier for the team...
Just my .02!
Brad
April 3, 2015 at 4:17 am
g.britton (4/1/2015)
Comments posted to this topic are about the item <A HREF="/articles/Queries/124140/">Using APPLY to make your queries DRYer</A>
Nice article! But I have non SQL comment:
leap year incorrect code:
CASE WHEN dt_year % 4 = 0 AND dt_year % 400 <> 0
leap year correct code:
CASE WHEN dt_year % 4 = 0 AND (dt_year % 400 = 0 or dt_year % 100 <>0)
April 3, 2015 at 5:03 am
Oops! Good catch!!
Gerald Britton, Pluralsight courses
April 5, 2015 at 9:14 am
Nice. Big win for maintenance. Smaller so a bit faster to develop. Smaller code a bit better compiler performance. Same query plan, no harm.
Thanks
April 7, 2015 at 5:08 am
brad.pears (4/2/2015)
I've never seen that particular SQL coding before and I will try to use that. I have seen tons of SP's and views here where I work with lots of repeating columns etc... exactly as described. THis leads me to believe that not a lot of folks out there are too familiar with it. THe only issue I can see is that sometimes to make things DRYer (hence more manageable/maintainable), the coding can get a little complicated due to the use of TSQL that not many are familiar with, so while it may improve coding in an effort to make more readable and manageable, for some folks, it may actually make things a little more complicated because they may not understand what is going on, which will then require some research and learning before they do understand and can make a change that will not adversely affect the functionality of the view or SP... Now this is fine generally, but sometimes when you are under the gun to implement a fix or change, having to learn a bunch of new stuff and then implement a change in that type of environment can be very stressful!!!Personally, I like this and I will use it, but we need to make sure that others we are working with also understand new functionality/coding techniques as well before it truly does make maintenance/manageability easier for the team...
Just my .02!
Brad
New stuff? APPLY was introduced ten years ago!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 7, 2015 at 5:31 am
Fabulous article. Very good read. 5 stars.
-- Itzik Ben-Gan 2001
April 7, 2015 at 6:41 am
I should have clarified. What I meant was new for the SQL environment and team you are working in/with...
April 7, 2015 at 12:48 pm
Can we use Group By 1,2,3, ... to make query DRYer it?
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
April 27, 2015 at 10:53 am
It's worth being aware that CROSS APPLY works kind of like an inner join, but OUTER APPLY works kind of like an outer join, so if your APPLY statement is for example a lookup from another table or a function which might sometimes fail to return a value, then you should use OUTER APPLY instead of CROSS APPLY, otherwise you will lose the entire row from the results. That makes CROSS APPLY rather different to a subquery directly within the SELECT.
;WITH TableA AS (
SELECT Job='Singer', FK=1
UNION SELECT Job='Guitar', FK=2
UNION SELECT Job='Bass', FK=3
UNION SELECT Job='Drums', FK=4)
,TableB AS(SELECT Id=1, Name='Mick', Age=68
UNION SELECT Id=2, Name='Keith', Age=66
UNION SELECT Id=3, Name='Bill', Age=69
UNION SELECT Id=3, Name='Paul', Age=71
UNION SELECT Id=5, Name='Charlie', Age=72
UNION SELECT Id=5, Name='Ringo', Age=73)
SELECT TableA.Job, OldestPlayer=OA.Name FROM TableA
OUTER APPLY (SELECT TOP(1) Name FROM TableB WHERE Id=TableA.FK ORDER BY Age DESC) OA --this will include 'Drums' row
--CROSS APPLY (SELECT TOP(1) Name FROM TableB WHERE Id=TableA.FK ORDER BY Age DESC) OA --using this instead would not include 'Drums' row because the lookup by Id failed
April 27, 2015 at 5:45 pm
gward 98556 (4/27/2015)
It's worth being aware that CROSS APPLY works kind of like an inner join, but OUTER APPLY works kind of like an outer join, so if your APPLY statement is for example a lookup from another table or a function which might sometimes fail to return a value, then you should use OUTER APPLY instead of CROSS APPLY, otherwise you will lose the entire row from the results. That makes CROSS APPLY rather different to a subquery directly within the SELECT.
Quite true of course, but rather off topic.
Gerald Britton, Pluralsight courses
April 28, 2015 at 3:41 am
The topic was teaching beginners how to move things into a CROSS APPLY from other parts of the query, so they can be reused instead of repeating their definition. The fact that depending on what you move into the CROSS APPLY, it can cause whole rows to silently disappear from the result set (when most beginners would simply be expecting a NULL to appear in the column, like it did before they moved it into a CROSS APPLY), seems an important concept to me. I remember being caught out by it when I started using CROSS APPLY and hadn't yet heard of OUTER APPLY.
April 29, 2015 at 12:55 pm
gward 98556 (4/28/2015)
The topic was teaching beginners how to move things into a CROSS APPLY from other parts of the query, so they can be reused instead of repeating their definition. The fact that depending on what you move into the CROSS APPLY, it can cause whole rows to silently disappear from the result set (when most beginners would simply be expecting a NULL to appear in the column, like it did before they moved it into a CROSS APPLY), seems an important concept to me. I remember being caught out by it when I started using CROSS APPLY and hadn't yet heard of OUTER APPLY.
Sorry, but that's not what the article is about. It's about using APPLY to evaluate expressions in the current row in the result set and assign aliases to those expressions in order to simplify other parts of the query (e.g. SELECT, GROUP BY, HAVING, WHERE etc.) Used this way, it is impossible for rows to "disappear" from the result set.
Gerald Britton, Pluralsight courses
Viewing 15 posts - 16 through 30 (of 59 total)
You must be logged in to reply to this topic. Login to reply