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