SQL SERVER – Interesting Observation – TOP 100 PERCENT and ORDER BY

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 (http://blog.sqlauthority.com)

About these ads

20 thoughts on “SQL SERVER – Interesting Observation – TOP 100 PERCENT and ORDER BY

  1. 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

    Like

    • Hi Luciano,

      This option will not work. Try following script out. It will not work. The whole behavior is not support with or without TIES.

      CREATE VIEW vwShift_ties
      AS
      SELECT TOP 100 PERCENT WITH TIES *
      FROM HumanResources.Shift
      ORDER BY ShiftID DESC
      GO
      — Select From View
      SELECT *
      FROM vwShift_ties
      GO

      Kind Regards,
      Pinal

      Like

  2. 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.

    Like

  3. 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

    Like

  4. 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

    Like

  5. 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????

    Like

  6. Pingback: SQL SERVER – SSMS Automatically Generates TOP (100) PERCENT in Query Designer « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #005 « SQL Server Journey with SQL Authority

  9. 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.

    Like

  10. 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.

    Like

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