Home Dashboard Directory Help
Search

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


Status: 

Resolved
 as By Design Help for as By Design


1
3
Sign in
to vote
Type: Bug
ID: 771145
Opened: 11/14/2012 4:22:05 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
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.
Sign in to post a workaround.