• Michael Meierruth (3/10/2011)


    Jeff,

    Neat stuff on hierarchies!

    One thing that's confusing me is the use of the back slash and how it orders.

    Thus in the script below, why do I get two different ordering results when I order on c1 as opposed to c2?

    select *

    from

    (

    select 'AA' c1,CAST('AA' as binary(4)) c2

    union all

    select 'A\',CAST('A\' as binary(4))

    ) t

    order by c1

    It's because of the collation settings for the server. For c1, it's purely an "alpha" sort based on collation. For c2, collation does not come into effect because it's binary. Since the backslash (ASCII character #92) has a larger ASCII value than the letter "A" (ASCII character #65), it sorts differently for the binary version. It would also sort c1 differently if you used one of the binary collations.

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