2008R2 Views with ORDER BY clause
in [Programming SQL]
|
Prev: How do I run this xpath query?
Next: A severe error occurred on the current command. The results,if any, should be discarded
From: Steve Hanna on 21 May 2010 23:35 I migrated a SQL Server 2000 database to 2008R2 and now my Views don't return the rows in the order specified by the ORDER BY clause. In researching this I found this article http://support.microsoft.com/kb/926292/. I have applied SP1 and the cumulative package 8 for SP1 and the view still returns rows in random order. Any help would be appreciated. Thanks Steve
From: Tibor Karaszi on 22 May 2010 02:23 Not sure how it applies to R2, but did you also apply the trace flag, as per the KB article? -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Steve Hanna" <gtmw(a)community.nospam> wrote in message news:3D6E6CCA-4AF8-4695-8477-9CD47122B8B1(a)microsoft.com... > I migrated a SQL Server 2000 database to 2008R2 and now my Views don't > return > the rows in the order specified by the ORDER BY clause. In researching > this > I found this article http://support.microsoft.com/kb/926292/. I have > applied SP1 and the cumulative package 8 for SP1 and the view still > returns > rows in random order. > > Any help would be appreciated. > > Thanks > > Steve
From: Gert-Jan Strik on 22 May 2010 05:53 I would say it is not a bug. When I look at the article it is not very clear how Microsoft thinks about it when it writes a "FIX" article with the text "This behavior is by design" in the Status section. My advice would be to fix the problem yourself. In other words, always use an ORDER BY clause in the outermost select whenever you want to return results in sorted order. That also means that you can remove all TOP 100 PERCENT .. ORDER BY from your views, since they do effectively nothing. If a view uses ORDER BY, then it is only there to determine which rows should be selected with the TOP clause. It does not / should not specify the output order. -- Gert-Jan Steve Hanna wrote: > > I migrated a SQL Server 2000 database to 2008R2 and now my Views don't return > the rows in the order specified by the ORDER BY clause. In researching this > I found this article http://support.microsoft.com/kb/926292/. I have > applied SP1 and the cumulative package 8 for SP1 and the view still returns > rows in random order. > > Any help would be appreciated. > > Thanks > > Steve
From: Erland Sommarskog on 22 May 2010 08:38 Steve Hanna (gtmw(a)community.nospam) writes: > I migrated a SQL Server 2000 database to 2008R2 and now my Views don't > return the rows in the order specified by the ORDER BY clause. In > researching this I found this article > http://support.microsoft.com/kb/926292/. I have applied SP1 and the > cumulative package 8 for SP1 and the view still returns rows in random > order. Yes, that is the execpted behaviour of views, unless you specify an ORDER BY clause when you retrive data from them. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: gareth erskine-jones on 22 May 2010 12:15
On Fri, 21 May 2010 20:35:01 -0700, Steve Hanna <gtmw(a)community.nospam> wrote: >I migrated a SQL Server 2000 database to 2008R2 and now my Views don't return >the rows in the order specified by the ORDER BY clause. In researching this >I found this article http://support.microsoft.com/kb/926292/. I have >applied SP1 and the cumulative package 8 for SP1 and the view still returns >rows in random order. A view, like a table, is an unordered set of rows. Of course, when you select the rows, they will come back in some order, but that order is an accident of the implementation, and shouldn't be relied upon. If you want the rows to be ordered, you need to add an order by clause to the select from the view. You can use order by within a view, but only when you are using TOP (or FOR XML): create view MyView as ( select top 10 someCol from SomeTable order by someCol ) Here the order by clause doesn't determine the order of the results set - it determines which 10 rows are included in the result set. The results happen to come out in that order, because SQL Server sorts them so it can take the top 10. I suspect you have a view like this: create view MyView as ( select top 100 percent someCol from SomeTable order by someCol ) SQL Server 2000 would dutifully sort the results set and then take the top 100 percent of the rows. In later versions of SQL Server, the optimizer has been improved - it knows that if you're taking the TOP 100 Percent, it doesn't need to sort the set to work out which rows to include - so it doesn't. and so the results come out in a different order. GSEJ |