Post reply

Set based approach to get previous rows value for same column which is a calculation

  • May 31, 2010 at 4:36 pm

    #1174602

    You bet. Thanks for the feedback and thanks for listening. Gotta run, though... I've gotta call OSHA and tell them handrails won't be necessary. 😛

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

  • June 1, 2010 at 12:58 am

    #1174649

    Couple of points to add:

    1. Many people think that parallelism is not possible with table variables. This is not so. It is true that the lack of statistics on table variables tends to make parallel plans less likely, but it is still quite possible (for read operations) especially if the table variable contains a significant number of rows and the OPTION (RECOMPILE) hint is added to the query. So, yes, MAXDOP(1) is definitely required here.

    2. About the ORDER BY thing, here's a quote from the SQL Server Query Optimisation Team blog:

    Other operations in SQL Server also have this “which rows qualify” semantic. ROW_NUMBER, RANK, DENSE_RANK, and NTILE contain an OVER clause in which an ORDER BY can be specified. This order by guarantees the output of the operation, but not the order in which the rows are output.

    You can find the full blog entry here: http://blogs.msdn.com/b/queryoptteam/archive/2006/05/02/588731.aspx

    The point is the the ORDER BY clause in the OVER clause is internal to the plan iterators that implement the ranking function. You may find that rows flowing from the iterators arrive at the next stage of the plan in the same order, but this is coincidence and depends entirely on the plan selected, and SQL Server's internal code implementation.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 2 posts - 16 through 16 (of 16 total)

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