Post reply

Sorting of Alphanumeric data

  • November 10, 2017 at 5:28 am

    #333633

    Dear Experts,
    Can anybody help understand how SQL server does sorting of the alphanumeric data in a character column types while referencing an index for it ?

    Thank you

  • November 10, 2017 at 7:49 am

    #1967524

    It's pretty simple, really.  SQL sorts character by character and left to right.  The character by character evaluation and order can be affected by the collation.  For example, some collations treat LL as a single character, whereas others do not.  It helps sometimes to look at it in binary, although binary will obscure the proper sorts for case or accent insensitive collations.  For example, running the following will show you why '10' sorts before '2'.

    SELECT CAST('10' AS BINARY(2)), CAST('2' AS BINARY(2))

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 13, 2017 at 3:19 am

    #1967729

    drew.allen - Friday, November 10, 2017 7:49 AM

    It's pretty simple, really.  SQL sorts character by character and left to right.  The character by character evaluation and order can be affected by the collation.  For example, some collations treat LL as a single character, whereas others do not.  It helps sometimes to look at it in binary, although binary will obscure the proper sorts for case or accent insensitive collations.  For example, running the following will show you why '10' sorts before '2'.

    SELECT CAST('10' AS BINARY(2)), CAST('2' AS BINARY(2))

    Drew

    Thank you Drew for helping understand.

Viewing 3 posts - 1 through 2 (of 2 total)

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