THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Sorted views...

Here's one that pops up regurarly. Something like:

"My views are no longer ordered in SQL Server 2005. I have ORDER BY in the view, but when I select from it, the rows are not returned according to the ORDER BY."

Since I tend to post a reply often enough to above type of posts, I decided to put it in writing once and for all, so I can point to this blog post. (Laziness is a virtue ;-) .)

A view is by definition not sorted. A view is supposed to behave like a table (and thanks to that we have the same language to operate against views as we have to operate against tables). This is why ORDER BY is not by itself allowed in a view definition.

It is allowed to have ORDER BY if you also have TOP, but the purpose of ORDER BY is now to make sure that the correct rows are returned (5 most expensive books, for instance), not in any particular order.

At some point in time, the "workaround" to have TOP 100 PERCENT popped up. The thinking was that we now are allowed to have ORDER BY, which makes the view "sorted". It doesn't. However, SQL Server 2000's optimizer wasn't as smart as 2005's optimizer, so for simple queries, you often found that data was returned according to your ORDER BY.

SQL Server 2005's optimizer is smarter, and if it finds TOP 100 PERCENT and ORDER BY it realizes that both these operations doesn't affect *which* rows to return, so both operations are removed from the execution plan. That is smart, since these doesn't affect the data anyhow. Remeber that a view is by definition not sorted.

Some developers has relied on the 2000 behavior to create "sorted views". This behavior was never documented, just a side effect of the execution plan, and was never guaranteed.

So what do we do? Well, same as when we run queries against a table. We have ORDER BY when we read data from the view!

Aside: The query builder which you can use when you create a view has a very strange behavior. It allow you to check a "sort" checkbox, and it will then add ORDER BY and TOP 100 PERCENT to the SELECT statement. According to above explanation, this is kind of silly, but I'm hoping that MS will tidy this up for the next version of SQL Server.

Published Wednesday, November 28, 2007 11:21 AM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mani said:

This behaviour is defind as a bug in SQL 2005.  and there is a fix available at

http://support.microsoft.com/default.aspx?scid=kb;en-us;926292&sd=rss&spid=2855

November 28, 2007 7:57 PM
 

TiborKaraszi said:

Thanks, I didn't know about that hotfix. However, this fix doesn't change the fact that views are not sorted. Check out the "More Information" section in the KB article. For instance:

"However, this hotfix is only a temporary resolution. After you migrate the application, you must update the application to work correctly with the new behavior in SQL Server 2005.

After you update the application, you can disable trace flag 168 and change the compatibility level of the database back to 90."

So, for instance, we need to run the db in compat 80 mode to get the 2000 behavior, along with a trace flag. The "pure" 2005 (and future) behavior is as according to my blog post: a view isn't sorted.

November 29, 2007 4:00 AM
 

AaronBertrand said:

As an addendum to Tibor's reply, please keep in mind that it is unlikely that this kludge will work in SQL Server 2008 and beyond, so please stop relying on ORDER BY in the view definition, with or without any short term fix.

November 29, 2007 10:27 PM
 

IDisposable said:

Or you can do the hack of changing the TOP 100 PERCENT to TOP 2147483647 and it'll sort without the hotfix... of course you're screwed if you have more than 2Gig rows...

That said, the sorted view is useful when doing paged results in a SQL 2000 compatible way... just count on the View to be orderd and letting the various TOP XX values in the classic TOP X NOT IN TOP Y nested query.  I think SQL Server 2005 will still sort those inner values since it should see the TOP of the outer.. but who knows.

November 29, 2007 10:57 PM
 

David Markle said:

The biggest issue, IMO, is that SQL 2005 still has a query designer that WRITES these bad queries, with these bad assumptions.  

Use of the query designer is probably one of the worst things you can do to your project.  Not only does it write illegible diharrea code, it writes *wrong* code.

I talked to MS about fixing the designer.  It wasn't on their list of priorities at the time.  I wonder if it's fixed in 2008.

November 30, 2007 8:00 AM
 

James said:

Such bullsh*t this whole issue. This was never properly documented in SQL2000, even worse it was 'sort of' recommended to use this top100 percent system. We complained bitterly about this to Microsoft about this lack of compatibility when 2005 came out and we were shouted down for using bad programming procedures. Now, many man months of recoding later, MS comes out with a fix.

December 3, 2007 3:41 AM
 

AaronBertrand said:

The theory is that some big customer gave MS enough grief that they came out with the fix to make them happy.  It's not exactly a simple fix... requiring both a patch AND a trace flag.  To their credit, they included the following text in the KB article:

"However, this hotfix is only a temporary resolution."

Implying that users better plan to update their applications before the /next/ version of SQL Server comes out.  The language could have been stronger and more persuasive, IMHO.

I agree that the query designer should be fixed, and I know that it has shifted to be a higher priority than it was during 2005 work.  I have not played with the most recent Katmai CTP to see how much they've done.

However, I disagree that this has been 'sort of' recommended at all, outside of the query designer.  Many of us have been espousing for years on the public newsgroups (even in the 2000 days) that this kludge is NOT the way to achieve consistent ordering, and is simply a lazy way to move the ordering logic away from the code that references the view.

December 3, 2007 10:19 AM
 

Erland Sommarskog said:

The "bug" is that ORDER BY in views should have been honoured in compat mode 80 from day 1, just like GROUP BY in compat mode 65 and 60 also implies an order. The trace flag is a bit overkill, but maybe they added it so that customers who have been running compat mode 80 without suffering from this, would not take the performance toll the "fix" may incur.

December 4, 2007 12:55 PM
 

Bob Barrows said:

Didn't I read somewhere about a nother kludge? I.E. using TOP 99.99 PERCENT instead of TOP 100 PERCENT?

Personally, I don't see the value of "sorted views".

December 4, 2007 1:22 PM
 

Steve Kass said:

I don't recommend trying to improve the kludge with TOP 99.99 PERCENT (only good if you have < 10000 rows) or TOP 2147483647, which the optimizer might eliminate also (now or in a future version) when it knows the table has fewer rows. I agree that the defective view designer is largely to blame, but It's also unfortunate that when Microsoft introduced the nonstandard TOP extention to SQL years ago, it used it with ORDER BY, giving ORDER BY a confusing second meaning. ORDER BY alone (which is prohibited in views) is a clause that affects the order in which results are returned to the client. The syntax TOP .. ORDER BY is something completely different, and is syntax to restrict the set of rows returned to those that are among the top ones in rank by some column or columns. A better syntax would have been SELECT TOP 100 (RANKED BY thisColumn, thatColumn) a,b,c FROM T. At least if this were the required syntax for TOP, there would be no suggestive message that ORDER BY requires a TOP specification in views. ORDER BY would simply be illegal in a view, as it should be.

December 4, 2007 2:57 PM
 

AaronBertrand said:

I agree with Steve that the dual functionality of ORDER BY can only be viewed in hindsight as a mistake.  TOP should have been implemented similar to the OVER clause introduced in SQL Server 2005.

December 4, 2007 6:44 PM
 

Aaron Bertrand : A theory on how "TOP" is determined in "TOP SQL Server Downloads" said:

December 4, 2007 7:05 PM
 

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM
 

Adi Cohn said:

I never understood why some programmers and DBAs are using ordered views, but I’m amazed that some people don’t learn there lesson.  For years I’ve been reading posts of MVPs and other known professionals that we shouldn’t use ordered views.  For some reason some people kept using it.  Now it doesn’t work and instead of trying to fix the views and applications, they found another way of ordering the view (using top 99.99 percent or using top 214748367).  What will happen when this will not work?  Why not just use the view as it was intended to be used – without ordering it?

Adi

February 4, 2008 3:04 AM
 

Estetik said:

This is exciting as many people at Microsoft have been working hard to make sure popular PHP applications work great on Windows.

May 14, 2008 5:08 PM
 

Daniel said:

Thanks for your documentation. I just ran into the same problem (SQL Server Express and Management Studio) and could not understand why my query was not sorted properly.

IMO, this is MS BS again, all other (real) DB systems handle sorted view in a correct way.

I WANT to use sorted views because I use the same view in different places and need to make sure it is in the same order everywhere.

March 18, 2009 2:48 AM
 

Aaron Bertrand said:

The other day, I showed you my approach (but not necessarily the best approach for everyone) to splitting

August 6, 2009 4:43 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement