THE SQL Server Blog Spot on the Web

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

John Paul Cook

Views and ORDER BY

The general rule is that a view can't be created using a statement that contains an ORDER BY clause. There is a workaround that some people believe works. Similar to my recent post on using an ORDER BY with UNION ALL, the empirical facts don't always agree with the truth. An ORDER BY can be added to a view definition if the view contains a TOP statement. But adding an ORDER BY clause to a view definition does not guarantee the order of the results, although many people believe that it does, just like UNION ALL doesn't guarantee ordered results. The truth remains - if you want to order the results of a select statement, add an ORDER BY statement to your select. In this example, I must add that an ORDER BY hidden inside a view definition doesn't do the job of an ORDER BY, although in your tests it might appear that it does.

If you want to order the results from a view, call the view with an ORDER BY clause instead of forcing an ORDER BY into the view definition.

There is a fix for SQL Server 2008 to cause the results from a view with TOP (with or without PERCENT) and ORDER BY to have ordered results. Fixes are no substitute for reading the documentation. Here is what the SQL Server documentation tells us:

"The ORDER BY clause is used only to determine the rows that are returned by the TOP or OFFSET clause in the view definition. The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself."

create table table1 (a int);
go

-- this won't work and the view won't be created
create view view1 as
select
a
from table1
order by a;
go

Msg 1033, Level 15, State 1, Procedure view1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

-- this will create the view
create view view1 as
select
top 100 a
from table1
order by a;
go

-- results might be in order or might not
select * from view1;

-- results will be in order
select * from view1
order by a;


 

 

 

 

 

Published Wednesday, March 06, 2013 8:16 PM by John Paul Cook

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

 

arse said:

try SELECT TOP 100 PERCENT instead

March 7, 2013 1:45 AM
 

Dave Ballantyne said:

Hi

Top 100 percent gets optimized out and has no effect.

BUT.....

Ordering in a view is contradictory to RDBMS practice.

The only place you should order is from a result set.

A view is not a result set.

You can select from a view to generate that result set and order that, but that is not the same as the view having order.

Infact, i would go so far to say that even if you use TOP(X) order by , the only guarentee is that those rows returned are the TOP(X) and you cannot rely upon the order of those.

March 7, 2013 6:31 AM
 

David Markle said:

We had this issue with SQL 2005 as well, up until the first or second service pack.  (I'd like to point out that the "we" I refer to is the community, and not the software I've written :D )

MS rightfully optimized out the TOP 100 PERCENT from views and tons of software which relied on this failed to work.  The root cause of all of this nonsense was that the drag n' drop view designer in SSMS actually GENERATED these view queries.  This pattern spread like a virus until it found its way into tons of software which promptly broke when the optimizer stopped ordering by views.

What do they need to do?  IMO the best solution is to completely disallow SELECT TOP 100 PERCENT ... ORDER BY in view definitions altogether and throw an error when the view is defined.  Such a view definition could and should still compile in compatibility mode, but it would force software which relies on this pattern to break when views are moved over to a new compatibility mode.

Of course, this wouldn't necessarily completely eliminate this problem, as you could always SELECT TOP <something bigger than the table will ever be> in a view to try to replicate the behavior, but those patterns are far, far less common than the SELECT TOP 100 PERCENT ... pattern.

March 9, 2013 10:26 AM
 

z said:

s

March 10, 2013 6:40 AM

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

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