Using an ORDER BY in a view


September 19, 2016 by Kenneth Fisher

For many years it’s been a best practice to never put an ORDER BY in a view. The idea is that a view shouldn’t have an inherent order. Just like any other query. If you want the data from a view ordered then you query the view with an ORDER BY clause. In fact if you put an ORDER BY in a view you’ll get an error:

Msg 1033, Level 15, State 1, Procedure MyView, Line 4 [Batch Start Line 2]
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.

But to get around this, for years people have been putting a TOP 100 PERCENT in the view as well as the ORDER BY. At that point the view works.

-- Set up demo table
CREATE TABLE [dbo].[table1] (id int NOT NULL PRIMARY KEY, col2 varchar(10));
CREATE UNIQUE INDEX [ix_table1] ON [dbo].[table1](col2);
INSERT INTO [dbo].[table1] VALUES (1,'abc'),(2,'def');
INSERT INTO [dbo].[table1] VALUES (3,'zzz'),(4,'aaa');
CREATE VIEW [dbo].[MyView] AS
SELECT TOP 100 percent *
FROM [dbo].[table1]

And the view gets created without an error. What I found interesting is that as far back as I can go (2008 R2) the ORDER BY is completely ignored.

SELECT * FROM [dbo].[MyView];


So there you go. Not only is putting an ORDER BY in a view considered a bad idea it has absolutely no effect.

27 thoughts on “Using an ORDER BY in a view

  1. mrxinu says:

    You rock, sir. Always dropping the science.

  2. Hahah… absolutely true… handy code to prove… not many people realise this, thinking they’re smart with the top 100% trick. Good post Kenneth!

  3. Stephen says:

    Interesting. i’ve seen this work successfully in the past, so I was suspicious. This will not work if you use TOP 100 percent, but it will work as desired if you use TOP 99.9999 percent.

  4. Rich M. says:

    Confirmed just now that I obtain the same result in 2005 Enterprise SP4 (modified to remove the row constructor INSERTs).

    Small typo: you created view MyView and then queried vw1.

    Nice quick article, thanks!

  5. thomasrushton says:

    2005 RTM – same problem. Fun times.

    I really don’t want to go check on one of the SQL2000 servers we have here. Don’t make me. (And don’t mention the SQL7 box in the basement…)

  6. AA04 says:

    As most of you are aware rows can come back in any order , there is no guarantee that a result set will come back in a certain order unless you use an Order by. “SELECT * FROM [dbo].[MyView];” is in fact 2 sql statements , the first is the inlinesql that is the view itself and the second is the select * mentioned above . So in the above statement the View will internally be processed and the data will be returned by ID but when sql engine applies the second statement ie the SELECT * … as there is no Order by there is no guarantee of the result set coming by in a particular order without an Order by clause ie “SELECT * FROM [dbo].[MyView] ORDER BY ID;” . Yes you do see rows coming back in the order of the view a lot of times but thats down to how sql engine processes the rows internally and as such you can not take it as Gospel that the rows will come back in the order stated in the view …

  7. Werner Staudacher says:

    With “TOP 9999999999” sorting allway works

  8. Richard says:

    With later versions of SQL Server, a View is never guaranteed an order even if top is used. The reason the Order By is allowed in Views with a TOP clause is to allow the view to return the correct “TOP” so many records as needed by the ORDER BY (but still not in any guaranteed order).
    As an example, if you have a data set that has 20 unique [Salesman ID] and [Total Order Dollars], and you want to see the top 5.
    SELECT TOP 5 [Salesman ID]
    ,[Total Order Dollars]
    ORDER BY [Total Order Dollars] DESC

    You will get the top 5 salesmen with the most sales order dollars, but of the 5 records returned, there will be no guaranteed order.

    This is how Microsoft implements SQL on SQL Server, and it wouldn’t make sense to try to work around it. Even if a workaround is found, Microsoft may eliminate it with the next release or service pack.

    There are circumstances when I would like to create a view with a honored ORDER BY. If my ORDER BY is always the same every time I reference that View, it seems redundant to have to specify an ORDER BY many times when I could specify it once when creating the View.

    • Currently you can in fact force an order using some of the workarounds mentioned in the comments. However you are correct, you can’t guarantee that this won’t change in any future release. As a general rule any query where you want an enforced order has to have the ORDER BY in the query.

      I’m really not sure why that confuses people so often. It certainly breaks enough code that you’d think people would learn.

      Thanks for the comment!

  9. LEWIS YANG says:

    Here is my solution to solve order by issue with TOP in view



    –In the below , I leverage OFFSET to provide the same function without issue

    OFFSET 0 rows


    –for more information , here is URL

    • *nod* There are a number of ways around the top 100 percent thing. Yours is certainly one of the more novel ones I’ve seen :). But the idea is that you SHOULDN’T work around it. Views shouldn’t have an inherent order. If for no other reason than you don’t know that in the next version of SQL your particular workaround won’t stop working.

  10. Piotr Rodak says:

    Well, there is an interesting fact that in SQL Server 2000 the ORDER BY in views was actually working. And it was a bug which many customers loved. When SQL Server 2005 came to being, the default behavior changed and all of a sudden application written with reliance on this ‘feature’ stopped working. Microsoft introduced a fix, that enables sorting in views by enabling a trace flag 168.
    That was a fix for applications built on SQL Server 2000 that relied on this feature. It should not be used in new applications. I don’t know if the flag still can be enabled in SQL Server 2016.

    There’s a nice post by John Paul Cook describing the issue.

  11. Steve says:

    I had to convert an old DB2 query to TSQL, DB2 does allow ordering on views.
    What this old query was doing was using the first view to create a detail report, and using the second view to dummy in a descriptive summary-total line at the very bottom (but using the same column names)

    I was too lazy to try and totally rewrite this entire query so I did use the 99.99999 percent trick.

    …there is ALWAYS a reason to need to sort and order. I can’t stand when someone tells me I should never have a reason to do something.

    • Hmm, you understand that unless the outer query has an order by you don’t have a guaranteed order correct? Even though the inner view does?

      • Steve says:

        Ordering on the outer query didn’t work in this case. Actually messes everything up. The ordering needed to be on the first inner view where the actual detail data was. The other view had no reason whatsoever to be included in sorting or ordering. Was kinda clever whoever did this old DB2 query.

        I’m sure I could jump through some hoops re-write the whole thing for TSQL limitations, but like I said…too lazy. Its not that important. 😉

        • *nod* I’ll be honest, I can’t think of a single way an inner query would have to be ordered in order to make an outer query work. But hey, I’ll be the first to admit I don’t know everything.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,746 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: