Home Forums SQL Server 2005 SQL Server 2005 General Discussion SQL Sorting Rules??? RE: SQL Sorting Rules???

  • August 15, 2014 at 3:05 pm

    #1738557

    Hi, thank you for your response.

    We need to provide functional specs for unit/business testing. On our documentation we assumed that the sorting was based on Unicode however this is not how the system behaves.

    We need to describe the actual behavior.

    Thanks again.

    Luis Cazares (8/15/2014)

    SQL Server doesn't order based on unicode values. The collation defines order to a certain point. Why do you need to understand the order of non-alphanumeric characters?

    Maybe you could use this as a test:

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max

    cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

    )

    SELECT N, NCHAR(N) ch

    FROM cteTally

    ORDER BY ch