Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112

    Unanswered: Selection from sorted subquery is unsorted in SQL Server 2005

    I have some relatively simple SQL that acts differently between SQL Server 2000 and 2005. Although it is easy to fix I'd like to know if this difference is expected (documented) or a bug, and if there is perhaps a setting/switch I can use to avoid a code review of hundreds of stored procs to look for similar scenarios.


    Executed the following script in SQL Server 2005

    CREATE TABLE #Floats
    (
    FloatID INT IDENTITY,
    FloatNumber FLOAT NOT NULL
    )

    DECLARE @sngCounter float
    SET @sngCounter = 20
    WHILE @sngCounter >= 0
    BEGIN
    INSERT INTO #Floats ( FloatNumber ) VALUES( @sngCounter )
    SET @sngCounter = @sngCounter - 1
    END
    SELECT * FROM (SELECT TOP 100 PERCENT * FROM #Floats ORDER BY FloatNumber) AS FloatNumbers

    DROP TABLE #Floats
    GO

    Produces the following resultset
    FloatID FloatNumber
    ----------- -----------
    1 20
    2 19
    3 18
    4 17
    5 16
    6 15
    7 14
    8 13
    9 12
    10 11
    11 10
    12 9
    13 8
    14 7
    15 6
    16 5
    17 4
    18 3
    19 2
    20 1
    21 0

    In SQL Server 2000 resultset is this
    FloatID FloatNumber
    ----------- --------
    21 0.0
    20 1.0
    19 2.0
    18 3.0
    17 4.0
    16 5.0
    15 6.0
    14 7.0
    13 8.0
    12 9.0
    11 10.0
    10 11.0
    9 12.0
    8 13.0
    7 14.0
    6 15.0
    5 16.0
    4 17.0
    3 18.0
    2 19.0
    1 20.0

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what exactly is the question? because it sure looks like both return exactly the same results

    are you talking about the .0 part? no?

    then it must be the order of result rows, yes?

    without an ORDER BY, order is indeterminate

    try putting the ORDER BY on the outer query and see what happens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    I got this from a MSDN forum:


    Using ORDER BY in a sub-query, and ORDER BY TOP 100 PERCENT in a VIEW were both undocumented 'abberations' that could be done in SQL 2000. They are not supported in SQL 2005.

    According to ANSI SQL standards, the resultset from a query has no guaranteed order UNLESS specifically set in order with the use of an ORDER BY statement at the outermost level.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks -- nice to know i got it right

    as for the "hundreds of stored procs" you need to look at, well, what can i tell ya, you shouldn't have been using "abberations" eh

    i always thought TOP 100 PERCENT was insane, and of course ORDER BY in a view doesn't make much sense either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Location
    Avon Lake, OH
    Posts
    112
    I'd fire those abberant SOBs that wrote the code except chances are pretty high that I was one of them!

    Thanks for your quick reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •