• It's by design and definition. Referring to the note at the following URL...

    [font="Arial Black"]OVER Clause (Transact-SQL)[/font]

    NOTE:

    If ORDER BY is not specified entire partition is used for a window frame. This applies only to functions that do not require ORDER BY clause. If ROWS/RANGE is not specified but ORDER BY is specified, RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame. This applies only to functions that have can accept optional ROWS/RANGE specification. For example, ranking functions cannot accept ROWS/RANGE, therefore this window frame is not applied even though ORDER BY is present and ROWS/RANGE is not.

    In other words, if you specify an ORDER BY for a function that CAN use a ROWS/RANGE specification, then the ORDER BY implicitly invokes a RANGE specification.

    As a bit of a sidebar, it would have been nice if they had it default to a ROWS specification because it's faster than the RANGE specification.

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