Is this true? (Order by goof up)
-
"SQL Server sometimes ignores columns specified in ORDER BY clause of a query if they are not included in the SELECT clause. This behavior is actually valid according to the SQL standard, but may be surprising."
I very much doubt it especially considering the word "sometimes" is used with it. Order by is not a hint but a directive, hence SQL Server cannot ignore it. Would like to know if theres any truth in it.
Thanks in advance.
What I hear I forget, what I see I remember, what I do I understand -
There are many different scenarios where an ORDER BY can be.
Exactly what does this quote mean?
Could you please provide an example?
Is it like:
SELECT a
FROM foobar
ORDER BY b
...or something else?
Also, what is the source of this quote?
As an end note, there are indeed situations when order by's don't do what you may expect.
Sorting is tricky business, and sometimes the 'by design' aren't that intuitive.
/Kenneth
-
Well I got that query in an email, so cant really pin down the real source.
I can imagine though the context in which it was asked to me. We are populating a temp table/table variable with data from other tables (sometimes unioned) and sometimes in a sequence
Eg:
INSERT INTO @GROUPSORTTABLE
SELECT * FROM @GROUPCOLLECTIONTABLE
UNION
SELECT * FROM @GROUPTABLE
order by GROUPID desc
SELECT * INTO #GROUPSORTTABLE
FROM GROUPCOLLECTIONTABLE
insert into # GROUPSORTTABLE
select * from @ GROUPTABLE
select * from #MAINTABLE1
ORDER BY GROUPID desc
The concern is that we need to generate an XML after this using the FOR XML Explicit clause, and if the sorting cannot be guaranteed to happen on a consistent basis then it could result in the dreaded “parent tag not opened” error message.
I came across an article which said that Order by does not work well with derived tables, inserts and sub queries, unless TOP is also specified.
If you are interested in can post the whole sequence of the problem in detail on here.
What I hear I forget, what I see I remember, what I do I understand -
I *think* I've heard somewhere that if you do something like: INSERT ... SELECT .. ORDER BY
..then no, you can't be 100% sure that the order by would *always* be honored... ie, the rows *may* be entered into the table in another order than the order by stipulates...
Though, it's nothing I can confirm, it's just something that's stuck in the back of my head.
afaik, the absolute sure thing when order by is always ordered (apart from collation issues) is when you do a simple SELECT.... FROM... ORDER BY, then the output will always be ordered as expected.
However... (uncertainty crawls in) if - the order by is by a column / expression *not* in the selectlist, then.. I'm not sure..
I could try to find out though... It's interesting.
/Kenneth
-
Have uilt hundreds of XMLs and never had problems with ORDER BY clause.
Only with building right resultset.
You probably mess column names in tables with column names in resultset for XML.
ORDER BY uses aliases assigned to columns in resultset by default.
My advise - NEVER use table column names in ORDER BY clause for XML. Order by node names, it's much more reliable, and node name is guaranteed to be unique.
_____________
Code for TallyGenerator -
Well I haven’t been fortunate enough with dealing with XML’s too much, but I believe generally when you use for Auto clause its fairly straight forward. The complexity creeps in when you are building an XML structure using the “for Explicit” clause, and more so when the hierarchies are dynamically added and deleted from the XML as per the requirement of the query.
Ordering is such cases is a tricky preposition and the only rule I follow is that the column which needs to be sorted should be included in all the child nodes of the resultant XML.
Thanks for the replies guys.
What I hear I forget, what I see I remember, what I do I understand -
Some comments on order by
SQL is allowed to ignore an order by in a subquery, if there is no top present. There is no order guarenteed in the following query.
SELECT * FROM Authors
INNER JOIN (SELECT * from books order by publicationdate) b on authors.authorID = b.authorID
While ordering an insert will work (as in, the records are inserted ordered), there's no guarentee of order when you select from that table, unless you add an order by clause.
Insert into #sometemptable
select * from sysobjects order by crdate
select * from #sometemptable
The order that the select returns is not defined. It might be by crdate, or it might not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
"Insert into #sometemptable
select * from sysobjects order by crdate
select * from #sometemptable
The order that the select returns is not defined. It might be by crdate, or it might not."
I'll be dead in that case.
I am relying on that fact that I will be inserting data into the temp table in a sequence eg:
Insert into #sometemptable
select * from tab1 order by crdate
Insert into #sometemptable
select * from tab2 order by crdate
Assuming that tab1 has one record with id 1 and table two has a record with id 2, I would expect the result of select * from #sometemptable to be
1
2
If this cannot be guaranteed, I will have to start looking for alternate solutions.
What I hear I forget, what I see I remember, what I do I understand -
The only way to guarentee the order of a resultset from a query is to put an order by on the query. You cannot depend on the order that the records are placed in a table, since records in a table have no defined order
For a simple query on a single table, the data will often be returned in either the order it was inserted or the order of the clustered index. However, if the query is run in parallel, or any operation occures that changes the record order ocurres or SQL uses a NC index instead of the cluster then the order will not be what you expect.
For some info on ordering (very good) from the Query Optimiser team
http://blogs.msdn.com/queryoptteam/archive/2006/05/02/588731.aspx
Bottom line. If you want your data ordered, use order by in the query that retrieves it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
nileshane,
you must ALWAYS have ORDER BY clause just before FOR XML EXPLICIT.
And this ORDER BY must contain node names, not column names in source tables.
And typically it will be big, no, BIG, sometimes terribly BIG ORDER BY clause, because structure of your XML depends absolutely on the ORDER BY.
I wish you never build xCBL for SAP systems, because they have enormous amount of useless nodes and subnotes which you must keep in right order. So my ORDER BY used to have more than 30 node names in the list.
So, take a breath an make it right way.
Little advise - comment FOR XML EXPLICIT first, it will help you to sort out your returning recordset.
As soon as it will lok all right you can turn FOR XML EXPLICIT back on and make a final check on your XML file.
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply