Today we will go over a very simple, but interesting subject. The following error is quite common if you use ORDER BY while creating any view:
Msg 1033, Level 15, State 1, Procedure something, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
The error also explains the solution for the same – use of TOP. I have seen developers and DBAs using TOP very causally when they have to use the ORDER BY clause. Theoretically, there is no need of ORDER BY in the view at all. All the ordering should be done outside the view and view should just have the SELECT statement in it.
It was quite common that to save this extra typing by including ordering inside of the view. At several instances developers want a complete resultset and for the same they include TOP 100 PERCENT along with ORDER BY, assuming that this will simulate the SELECT statement with ORDER BY.
In SQL Server 2008, this behavior is corrected by ignoring the ORDER BY clause when TOP 100% is used. Let us see a quick script for the same.
USE AdventureWorks
GO
-- SELECT from Table Without TOP or ORDER BY
SELECT *
FROM HumanResources.Shift
GO
-- SELECT from Table With TOP and ORDER BY DESC
SELECT TOP 100 PERCENT *
FROM HumanResources.Shift
ORDER BY ShiftID DESC
GO
-- Create View with same as above statement
IF EXISTS (SELECT * FROM sys.views
WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[vwShift]'))
DROP VIEW [dbo].[vwShift]
GO
CREATE VIEW vwShift
AS
SELECT TOP 100 PERCENT *
FROM HumanResources.Shift
ORDER BY ShiftID DESC
GO
-- Select From View
SELECT *
FROM vwShift
GO
Let us see the result of above script here. It is quite clear from the result that in the view, ORDER BY clause is ignored and result is returned as similar as that of a regular SELECT statement without ORDER BY.
Note that SQL Server 2008 view with ORDER BY clause does not throw an error; moreover, it does not acknowledge the presence of it as well.
If you have any similar ideas as the above one, please leave a comment here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
17 Comments. Leave new
Hi Mr.Dave,
Good Morning. I am new in sql 2008 before that i have 2 years experience in Sql 2000. I am working at Private concern as Software Engineer.
In oracle we can create a JAVA class file and we can call that JAVA file in procedure, functions and packages also. My doubt is same like Can we create C# class file in Sql 2008. If possible pls explain if you have a time….
Bye
Thanks & Regards
Kalicharan
Hi Pinal,
As far as I remember, when using “TOP 100 PERCENT” clause, one must include “WITH TIES” to assure all rows are returned
Regards,
This was classified a bug by microsoft and has been fixed in a hot fix ( https://support.microsoft.com/en-us/help/926292) which was rolled up in Cumulative update package 1 for SQL Server 2008
https://support.microsoft.com/en-us/help/956717/cumulative-update-package-1-for-sql-server-2008/
This is also a problem in SQL 2005
This is not a bug; it was removed on purpose:
And there are workarounds, such as the following from Adam Machanic:
Please notify me of new posts via email.
@Probal Roy
To be emailed changed on a particular post, replay to this message, and check the option “Notify me of new posts via email.”.
Hi All,
You can use:
” Top (99) Percent”
or
“Top (200000000)” I mean a large number that is 1000 times more than your selection records and be sure that all of your selection would be ruturned.
It works!
Just Try It.
:)
Best Regards,
Mahboubeh J.
Hi Mahboubeh,
Using TOP 99.99 PERCENT or large integer was producing another issue such as performance.
regards,
gue
Hi all,
TOP 100 PERCENT doesn’t work for me on SQL 2008 R2, so I have used TOP 99.99 PERCENT to fix the issue.
Has anyone tried to install the Microsoft hotfix for this?
Cheers :)
Mohammad
Not sure about the hotfix, my workaround was (using Pinal’s sample from above):
SELECT TOP 100 (select count(*) from HumanResources.Shift ) *
FROM HumanResources.Shift
ORDER BY ShiftID DESC
In 2008 does it still work in stored procedures?
I didnt get how percent would be work from inside.
coz when i tried it using view there is no effect of order by
so for what purpose we use it????
Simple and nice explanation of TOP 100 + ORDER BY in view, which is useless for ordering.
Hi all,
I used TOP 99.99 and it works
TOP 100 PERCENT doesn’t work for me on SQL 2008 R2, so I have used TOP 99.99 PERCENT to fix the issue.
Pinal, have an issue I haven’t seen addressed anywhere. If you have no answer for this, then no such answer exists.
I’m replacing old ‘Set ROWCOUNT’ code found in a proc. I need to account for both X number of rows needed, and ALL rows needed, something ROWCOUNT handled with a parameter set to 0. How can I cleanly use ‘Top (@RowCountParameter)’ in a single select statement, when that parameter might be passed 0 for all rows?
So far, the cleanest way I could think of was, if 0 was passed, get a count of all rows produced by the select query and change @RowCountParameter’s value to that count.
That however requires copying the ‘from’ and ‘where’ parts of the query and running it twice, which I want to avoid having to do.
I also considered just setting the parameter to a hard coded max value for its data type, but I don’t like relying on a standard value that could change some day, however remote and unlikely that situation might be.
What I really need is a clean way to swap ‘Top (@RowCountParameter)’ with the equivalent of ‘Top 100 Percent’ without having to make all the query code dynamic, or duplicate code.
It’s incredible to me that Microsoft would deprecate a useful feature without providing a clean replacement for it.
Thank you in advance, win or lose.