SQL SERVER – Fix : ERROR : Msg 1033, Level 15, State 1 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.

Following error is encountered when view is attempted to created with ORDER BY clause in it. ORDER BY clause is not allowed in views in SQL Server 2005. This solution also displays the workaround to use ORDER BY in VIEW. I really do not prefer to use views. My views on SQL Views read it SQL SERVER – Restrictions of Views – T SQL View Limitations.

Msg 1033, Level 15, State 1
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.

This is error interested me as workaround of this involves some programming changes how the view is called in T-SQL query.

Fix/Solution/WorkAround:
Do not use ORDER BY clause in View when not using TOP or FOR XML in SELECT clause. If you have to use ORDER BY use it in SELECT clause where view is used.

Let us understand using simple example on AdventureWorks database.

Following script will generate error in Query Editor.
CREATE VIEW vTestContactView
AS
SELECT
FirstName, LastName
FROM Person.Contact
ORDER BY FirstName DESC
GO

Msg 1033, LEVEL 15, State 1, PROCEDURE vTestContactID, 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

.
Now there is are two ways to fix the error.

Method 1: The Smart One and works all the time.
If you do not want to change your all query which is using this view do following changes in view. It will return the same result without much (or none in most cases) performance loss. This is really helpful when ORDER BY Clause uses column which is not in SELECT clause(second example).
----First example
CREATE VIEW vTestContactView
AS
SELECT TOP
100 PERCENT FirstName, LastName
FROM Person.Contact
ORDER BY FirstName DESC
GO
----Second example
CREATE VIEW vTestContactIDView
AS
SELECT TOP
100 PERCENT FirstName, LastName
FROM Person.Contact
ORDER BY ContactID DESC
GO

Method 2: The Technically Correct but only works when you have to ORDER BY on clause which is used in SELECT of view.
First create view changing without using ORDER BY clause.
CREATE VIEW vTestContactID
AS
SELECT
FirstName, LastName
FROM Person.Contact
GO
Call the
VIEW using
ORDER BY clause.
SELECT *
FROM vTestContactID
ORDER BY FirstName DESC

I really enjoyed writing the solution for this problem.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

9 thoughts on “SQL SERVER – Fix : ERROR : Msg 1033, Level 15, State 1 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.

  1. It is worth mentioning that Method 1 will only work in SQL Server 2000 or SQL Server 2005 with a hotfix, traceflag and compatibility 80. Granted, in 2005 in won’t kick out an error when you use TOP (100) PERCENT … ORDER BY but without the hotfix the query engine will actually ignore it and return the result unordered.

    See KB 926292 for information on restoring the ORDER BY behavior that is present in SQL Server 2000

    Like

  2. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 2 Journey to SQL Authority with Pinal Dave

  3. Example-2 really helped. Mine had all sort of pivots and stuffs and a bit more complicated but managed to get it working the way it is supposed with Example-2. Great!

    Like

  4. Can someone help mi with this my query. I have same error what you describe :

    id,
    meter_id,
    meter_serialnumber,
    timestamp,
    transponder_offset,
    reading,
    readingstatus_des,
    consumer_firstname,
    consumer_lastname,
    transpondertype,
    readingzone_zone,
    readingzone_subzone,
    meter_remark,

    Like

  5. Hi Pinal,

    My requirement is first execute Order By and after that get some records between them for Pagging.

    Select emp1.* from (
    select * from emp order by username
    ) as emp1 where emp1.empID > 10 or emp1.empID < 20

    How Can I Do this ?

    Thanks
    Mitesh Darji

    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