SQL Server: A query with an ORDER BY clause cannot be used as a table expression

Hi Friends,

Assume an Orders table with 2 columns (OrderID, customerID) and we try to write the following query:

USE NORTHWIND
GO
 
SELECT *
FROM (SELECT orderid, customerid
      FROM dbo.Orders
      ORDER BY orderid) AS O

If you try to execute the above query, it will not work. I asked you the reason and the solution to make this work.

The reason why this does not work is becuase ORDER BY clause returns a cursor rather than a relational result set. The outer query expects a table (relational result set) but the table expression in the inner query is returning a cursor causing an error.

If you want this query to work, include a TOP clause in the inner query as follows:

USE NORTHWIND
GO
 
SELECT *
FROM (SELECT TOP 100 PERCENT orderid, customerid
      FROM dbo.Orders
      ORDER BY orderid) AS D;

Mind you, TOP is not an ANSi standard. Its T-SQL specific. TOP clause will convert the cursor back to a result set. Its interesting to see how nonstandard tricks can be applied to get the code working.

Also note that ORDER BY is an expensive clause. Use it only if you required.

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

7 Comments on “SQL Server: A query with an ORDER BY clause cannot be used as a table expression”

  1. 6/12/2011 5:06:07 PM Sachin Nandanwar said:

    Amit,

    I am afraid I will have to disagree with you on this.SQLServer or any RDBMS works on fundamentals of Set theory and in Set theory there is no specific order for sets.It has nothing to do with cursors returned by the inner query but more to the mathematical aspect the way RDBMS is designed.

    The first query does not works because SQL interprets it as a set and the basic property of a set is that a set cannot have a ordered by resultset, while the second query works because SQL interprets it as a sequence which has to be ordered and can be filtered (TOP by clause).

    Sets and sequences are related to Set Theory.Its a branch of Pure Mathematics.Try googling it to understand the basic concepts.

    Now let us look at the RDBMS point of view.

    Basically order by works on a resultset/query and not on a table.Thats why we cannot mention a order by clause when designing a table.When you write some query like this

    1 select * from

    2 (

    3 select * from mytable

    4 )T
    What exactly you are doing is that you are creating a virtual table T and querying the table T and not the resultset of the inner query.Thats why you have to mention an alias T when you use a query like above.

    Thats the reason you cannot specify a order by clause in table but can specify it in a view.Because view acts as a virtual table in your query same for CTE’s as well .

    Now you might ask then why TOP by clause works.It works because TOP by clause forces SQL server to interpret inner query as a sequence and not as a set which needs to be ordered.Thats why a TOP by clause always needs a order by clause without which it will error out.

  2. Great going friends… Whatever it may be, but we, poor people, got to know some unknown facts Innocent. But please clarify whether we should follow Galileo or Aristotle…Undecided

  3. Hey Sachin,

    To find the answer to this question, I also started exploring the Solar System (T-SQL Engine) within the Galaxy (SQL Server) and came across some interesting facts…

    First of all what I have found is that the query which is tweaked to allow “Order By” clause in a derived table using a TOP option does not actually order the data.

    –Query 1

    SELECT *

    FROM (SELECT TOP 100 PERCENT orderid, customerid

    FROM dbo.Orders

    ORDER BY orderid) AS D;

    –Result 1

    orderid customerid
    10643 ALFKI
    10692 ALFKI
    10702 ALFKI
    10835 ALFKI
    10952 ALFKI
    11011 ALFKI
    10308 ANATR
    10625 ANATR
    10759 ANATR
    10926 ANATR

    Now run the following query which actually sorts the data using orderid,

    –Query 2

    SELECT orderid, customerid

    FROM dbo.Orders

    ORDER BY orderid

    or

    SELECT TOP 100 PERCENT orderid, customerid

    FROM dbo.Orders

    ORDER BY orderid

    –Result 2
    orderid customerid
    10248 VINET
    10249 TOMSP
    10250 HANAR
    10251 VICTE
    10252 SUPRD
    10253 HANAR
    10254 CHOPS
    10255 RICSU
    10256 WELLI
    10257 HILAA

    Note: I have shown only the first 10 rows

    This means that though SQL Server allows using Order By clause along with the TOP option inside a View / Derived Table, it does not gurantee the order of the data. The same used to work in SQL Server 2000 but SQL Server 2005 onwards, the optimizer completely ignored the combination of TOP 100 PERCENT and the ORDER BY clause. The optimizer realizes that TOP and ORDER BY are meaningless here; therefore, it ignores the sorting of the data.

    Now turn on the option to “Include Actual Execution Plan” in SSMS and you will find that Query2 uses a Clustetred Index PK_Order which Query1 does not.

    SQL Server 2005’s Books Online also has a note saying, “When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.”

    Thanks,

    Amit Karkhanis

Leave a Reply to Amit Bnahal Cancel reply

Your email address will not be published. Required fields are marked *