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.
7 Comments on “SQL Server: A query with an ORDER BY clause cannot be used as a table expression”
6/12/2011 5:06:07 PM Sachin Nandanwar said:
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
3 select * from mytable
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.
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
On sqlservergeeks who is Galileo and who is Aristotle ????
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.
FROM (SELECT TOP 100 PERCENT orderid, customerid
ORDER BY orderid) AS D;
Now run the following query which actually sorts the data using orderid,
SELECT orderid, customerid
ORDER BY orderid
SELECT TOP 100 PERCENT orderid, customerid
ORDER BY orderid
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.”
Just a qucik reply here.
Thats the reason I said you to add an order by clause https://www.sqlservergeeks.com/forums/microsoft-data-platform/sql-server/62/getting-time-for-each-log
6/24/2011 12:43:58 AM Amit Bansal said:
Sachin, reply coming to this one shortly 😉