Using APPLY to make your queries DRYer

  • Never knew this--thanks for the article!

  • 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

  • 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.

  • 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

  • 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)

  • Oops! Good catch!!

    Gerald Britton, Pluralsight courses

  • 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

  • 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!


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • Fabulous article. Very good read. 5 stars.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • I should have clarified. What I meant was new for the SQL environment and team you are working in/with...

  • Can we use Group By 1,2,3, ... to make query DRYer it?

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • 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

  • 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

  • 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.

  • 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