Using an ORDER BY in a view

22

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);
GO
INSERT INTO [dbo].[table1] VALUES (1,'abc'),(2,'def');
INSERT INTO [dbo].[table1] VALUES (3,'zzz'),(4,'aaa');
GO
CREATE VIEW [dbo].[MyView] AS
SELECT TOP 100 percent *
FROM [dbo].[table1]
ORDER BY id;
GO

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];
GO

ORDERBYInAView

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

22 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]
    FROM
    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

    IF OBJECT_ID(‘EMP’) IS NOT NULL
    DROP TABLE EMP
    GO
    CREATE TABLE EMP
    (EID INT, ENAME VARCHAR(30) , EDT DATETIME)
    GO

    INSERT INTO EMP(EID,ENAME,EDT) VALUES(3,’NANCY’,’2016-09-05′)
    INSERT INTO EMP(EID,ENAME,EDT) VALUES(1,’ADA’ ,’2016-11-03′)
    INSERT INTO EMP(EID,ENAME,EDT) VALUES(5,’SARAH’,’2016-09-04′)
    INSERT INTO EMP(EID,ENAME,EDT) VALUES(2,’JULIA’,’2016-08-23′)
    INSERT INTO EMP(EID,ENAME,EDT) VALUES(4,’JANE’ ,’2016-10-30′)
    GO

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

    IF OBJECT_ID(‘VW_EMP_OFFSET’) IS NOT NULL
    DROP VIEW VW_EMP_OFFSET
    GO
    CREATE VIEW VW_EMP_OFFSET
    AS
    SELECT EID,ENAME,EDT FROM EMP
    ORDER BY EID
    OFFSET 0 rows
    GO

    SELECT * FROM VW_EMP_OFFSET
    GO

    –for more information , here is URL
    https://www.facebook.com/groups/222546864546011/permalink/912754748858549/

    • *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.
    http://sqlblog.com/blogs/john_paul_cook/archive/2013/03/06/views-and-order-by.aspx

    • That is interesting! It’s amazing how many workarounds there are for something that technically was a workaround to allow people to do something they shouldn’t have been doing in the first place 🙂

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

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

Join 2,146 other followers

Follow me on Twitter

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