Understanding and Using APPLY (Part 1)

  • Arjun Sivadasan (4/12/2010)


    ...They all showed me the same execution plan. Am I not 'seeing' something?

    Hey Arjun, no you are not missing anything - but I do cover all these questions in Part 2 in some detail. These are all great questions, but please bear with me until next week's concluding part.

  • this article gave me an idea about the problem I'm working on....Most of all, I like the way the article is presented.

  • I will join the praise of the article. Very thought provoking. I am looking forward to Part II and I am wondering if I am going to have to rethink my standard logic approach to many problems. Challenging standard thought processes and finding new and improved ways to tackle problems is one of the primary benefits to SSC.

    Thanks.

  • Good stuff. Looking forward to part 2.

  • BTW Paul, I really like the way you did the data diagram with grids under the tables (so much that I'll probably utilize that technique in any future articles that I might write :-D).

    I see that the function utilizes "WITH SCHEMABINDING". Is this a necessary step?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Great article Paul! Looking forward to next weeks article.

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • This is the best article on APPLY that I've read. I particularly appreciate the paragraph: "APPLY calls the function once for each row from the input. ...to produce the eventual full result." It is very well worded and clear.

    Usually I adopt new T-SQL offerings in each version right away, making great use of them. For example, I started using Output and FOR XML the second they were available to me. But no matter how many articles I have read on APPLY, I have yet to use it. Perhaps with your articles I will at least see why people like them and perhaps find a use for it myself.

    Thanks for taking the time to do this. This is one of those areas for me that is a "hole". It is a beginning concept, but I just haven't gotten it yet.

  • Paul White NZ (4/10/2010)


    I would like to express my sincere thanks to the following people, for their help in producing this article:

    Amanda Lawrence

    Jeff Moden MVP

    Chris Morris

    Lutz Müller

    Jason Brimhall[/url]

    Thank you everyone!

    Paul

    You are quite welcome. It was a pleasure.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Great article Paul.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Nicely done, Paul. The impeccably timed/highly appropriate graphics, the simple code examples, and the straight forward "talk" make this a model for future articles. It was a pleasure to read and, judging from the discussions so far, it looks like it really hit the mark for understanding. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • CirquedeSQLeil (4/12/2010)


    Paul White NZ (4/10/2010)


    I would like to express my sincere thanks to the following people, for their help in producing this article:

    Amanda Lawrence

    Jeff Moden MVP

    Chris Morris

    Lutz Müller

    Jason Brimhall[/url]

    Thank you everyone!

    Paul

    You are quite welcome. It was a pleasure.

    The same from my side! Additionally, I'd like to thank you for giving me the opportunity to participate in the "evolution" of that very fine article. I learned a lot.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Really nice article.

  • Thanks Paul! Very well organized and written. Plus I learned something new. 🙂 I'm looking forward to Part 2.

    ---------------------------------------------------------------------
    Use Full Links:
    KB Article from Microsoft on how to ask a question on a Forum

  • WayneS (4/12/2010)


    BTW Paul, I really like the way you did the data diagram with grids under the tables (so much that I'll probably utilize that technique in any future articles that I might write :-D).

    Glad you found it helpful - I would be flattered to see you use the idea in your next article.

    I see that the function utilizes "WITH SCHEMABINDING". Is this a necessary step?

    There are some notes on this in the attached scripts - but essentially, no it is not required as such - but I do recommend it.

    In general, I always schema-bind functions, unless there is a very good reason not to. Not only does it prevent the schema of referenced entities changing unexpectedly, it can also have significant optimization benefits.

    If a function (scalar, iTVF, or multi-statement) is not marked as schema-bound, SQL Server marks it as non-deterministic, as performing user data access, and as performing system data access. This is a performance optimization - to avoid having to check the function and all dependent objects for these properties on every call.

    This can have important performance consequences, since the Query Optimizer has many fewer plan options when it encounters a non-deterministic element. Marking a function as schema-bound forces the engine to statically check the function in detail to determine if it is deterministic or not.

    For function that do not do data access, being marked as doing so can hurt UPDATE performance in particular - see http://blogs.msdn.com/sqlprogrammability/archive/2006/05/12/596424.aspx.

    You can check the properties of a function using the OBJECTPROPERTYEX built-in function:

    SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.GetSubjectsAndScores', N'IF'), N'IsDeterministic');

    SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.GetSubjectsAndScores', N'IF'), N'UserDataAccess');

    SELECT OBJECTPROPERTYEX(OBJECT_ID(N'dbo.GetSubjectsAndScores', N'IF'), N'SystemDataAccess');

    Paul

  • dbowlin (4/12/2010)


    I will join the praise of the article. Very thought provoking. I am looking forward to Part II and I am wondering if I am going to have to rethink my standard logic approach to many problems. Challenging standard thought processes and finding new and improved ways to tackle problems is one of the primary benefits to SSC.

    This comment pleases me immensely. One of the primary aims of the article is to get people thinking about how APPLY can be used to approach some types of problems from a new angle.

Viewing 15 posts - 16 through 30 (of 89 total)

You must be logged in to reply to this topic. Login to reply