Using an ORDER BY in a view
27September 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
So there you go. Not only is putting an ORDER BY in a view considered a bad idea it has absolutely no effect.
SQL Studies 
You rock, sir. Always dropping the science.
Thanks 🙂
NOT APPLY ON UNIQUE INDEX AND SEE RESULT (INDEX APPLY AS REQUIREMENT)
ORDERING WILL WORK ON VIEW
That’s not going to be guaranteed though. Without an ORDER BY you MIGHT get the results in the right order and you might not. In particular if you are relying on the existence of an index to give you the right order what happens when that index changes or goes away? Or a new one gets put in that SQL decides to use instead?
Hahah… absolutely true… handy code to prove… not many people realise this, thinking they’re smart with the top 100% trick. Good post Kenneth!
Thanks! Always fun to find something unusual.
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.
*nod* there are several ways around it. And it was “fixed” in 2005. https://blogs.msdn.microsoft.com/queryoptteam/2006/03/24/top-100-percent-order-by-considered-harmful/
My guess is when they fixed it they specifically checked TOP (100) PERCENT and didn’t check any of the (several) other ways to get around it.
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!
Thanks for mentioning the typo! Fixed. And yea, it appears to be something fixed with SQL 2005.
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…)
Oh, heck with it. SQL 2000 RTM – that does return the data in id order.
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 …
With “TOP 9999999999” sorting allway works
*nod* There are certainly ways around it. As stated in some of the other comments you can also use TOP 99.9999 PERCENT etc.
For me, “Top ” works for sorting, but “Top percent” does not.
Tested with 2008 and 2012
Aniway i dont use it.
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!
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.
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 🙂
lol, that was a good one Kenneth. Thanks for your article as well.
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?
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.