select view result not in order after SQL server 2008 SP3 CU2 by Momoco

Resolved

as By Design Help for as By Design

Type:	Bug
ID:	771145
Opened:	11/14/2012 4:22:05 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

we have a view like these

view name: ViewA
select top 100 percent ColA from TableA order by ColA

Before we apply SQL Server 2008 SP3 CU2 (10.00.5766─>10.00.5768)
The result of select view was in order, but after apply it doesn't work anymore.
even after apply CU7, still have the same problem.

I've tried to reproduce this problem in different test environments, all the same, view in order worked before CU2, not after.

Details (expand)

English

SQL Server 2008 SP3

SQL Engine

Windows Server 2008 R2 Enterprise

English

1.SQL Server 2008 SP3 CU1, build a view with order by, select result in orders
2.apply CU2, select view result not in order.
3.apply CU7, select view result not in order.

select view result not in order

select view result in order

X64

0 attachments

Posted by Microsoft on 11/28/2012 at 9:09 AM

Thanks for taking time to file your problem. As someone else commented, it is necessary to include an "order by" clause in a query in order to guarantee the desired order. Note that the "order by" clause cannot appear inside the definition of the view itself, it must be in the query that references the view. Please let us know if this does not resolve your issue.

Campbell, SQL Development

Posted by SAinCA on 11/27/2012 at 4:25 PM

Please refer to Books Online at http://msdn.microsoft.com/en-us/library/ms188385.aspx, which clearly states, in the first bullet point isolated here:

The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

In other words, you have been "lucky" until now to get your rows in order without the order by - your luck just ran out... :-(

Upshot: Add the requisite ORDER BY that should have been there in the first place if record sequence was an issue.

Microsoft