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.
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.
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 ????
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
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
sac.nan@gmail.com
6/24/2011 12:43:58 AM Amit Bansal said:
Sachin, reply coming to this one shortly 😉