THE SQL Server Blog Spot on the Web

Welcome to - 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);

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

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
top 100 a
from table1
order by a;

-- 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 6, 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



arse said:

try SELECT TOP 100 PERCENT instead

March 7, 2013 1:45 AM

Dave Ballantyne said:


Top 100 percent gets optimized out and has no effect.


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:


March 10, 2013 6:40 AM

sushil said:

thanks a lot, it worked for me well :)

April 25, 2014 1:43 AM

S.A.BABOU said:

Here is a solution for SQL Server 2016:

create view view1 as

select top (select count(*) from table1) c1,c2,c3

from table1

order by c1;


August 24, 2017 8:39 AM

Bernd said:

You can use the OFFSET-Syntax.

CREATE VIEW viewWithOffset



October 24, 2017 4:45 AM

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in 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. John is also a Registered Nurse currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement