Order by inconsistencies | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Order by inconsistencies

I’m experiencing inconsistencies with the "Order By" clause that I can’t explain. Here’s the situation: Table:
create table blah (
Document varchar(50) not null,
UniqueID varchar(50),
Path varchar(500),
Blah2 varchar (250),
Blah3 varchar(255),
Blah4 varchar(50),
AutoID int,
MoreBlah varchar(255)) This table has no indexes or keys or constraints. There are >200,000 rows of data. Now what I’m trying to do is run a "Select into" using the "Order by" clause, which should sort the data as it inserts. Let’s say that I’m ordering by the UniqueID column, which should be unique, although it is not enforced by the database. So I run: select Document, UniqueID, Blah2, Blah3
into test
from blah
order by UniqueID So this is where the inconsistencies comes in. Sometimes it sorts the data correctly. Sometimes it doesn’t. When I first run this select, it doesn’t sort by UniqueID like I want.
If I run: Select UniqueID, Document, Blah2, Blah3
into test
from blah
order by UniqueID it will insert according to the order by column as expected. When I run the first select that didn’t work, it now sorts correctly. If I now add another column to the select into: Select Document, UniqueID, Path, Blah2, Blah3
into test
from blah
order by UniqueID It will once again sort incorrectly. If I go in and put an index on UniqueID, and then run the same statement, it now sorts correctly. SQL BOL tells me that there may be an issue sorting incorrectly if order by text/ntext, but that’s not the case. Also, there is a potential issue if the rowsize (or is it total columns sorted?) is more than 8,060 bytes. But again that’s not the case here.
Can anyone explain why "Order by" is not sorting as it should?
You haven’t posted the SELECT statement, with which you retrieve the data. That statement needs to have the ORDER BY clause, otherwise there is no guarantee that rows are returns in any meaningful way. The fact that you INSERT the rows in a specific order doesn’t mean the are automatically always returned in that way. SQL Server will try to return the rows in the fastest possible way.
Even the presence of a clustered index is no guarantee for a correct sorting, although it mostly seems so. If you need a specific order you have to use ORDER BY. —
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?http://www.insidesql.de/blogs

Frank, you bring up a good point. My underlying assumption had been that the data returned from a select would be the same order that it was inserted. Hence, if I inserted into a table ordering by a certain column, then the data would be returned in the same manner. Thus far, that has been the expected behavior. This is the first time that we have experienced behavior that is inconsistent. But to answer your question, I’m validating results by doing a straight select. I.e. Select * from test When I used the order by:
Select * from test
order by UniqueID then yes, the data is returned in the correct, and expected order. Currently what I need to do is to be able to explain to my superiors why this situation would occur, since it’s the first time that we’ve experienced this inconsistency and the code hasn’t changed. The followup questions would be:
– What is the default way that SQL Server returns data?
– In what situations would data from a table not be returned in the same order that it was inserted?
The default way that SQL Server returns data is not in any particular order. That is: provided that you do not specify the order, using an ORDER BY clause. Totally predictable, no inconsistency. The consistency is that there is only specific order if you ask for one. There might be an order present in a rowset returned by a query without an ORDER BY clause, but that would not be deliberate on the part of the database engine. The flipside to that is that the database cannot deliberately mess up the order when you do give it an ORDER BY clause.
Thanks for the info guys! I feel like such an ignoramus. Heh.
Okay, one more followup question: If I run an alter on the table to add an indentity column, for example: ALTER TABLE test ADD AutoID INT IDENTITY (1,1) What is the expected behavior? Will it also utilize the same unordered result set (as select * from test) and assign identity at will? What is the best way to add the identity to the table such that I can associate the identity with the ordering that I would like? Thanks!
Still the same rule applies: you have to ask SQL to "ORDER BY". So the proper way to get an ordered identity value (which is of no use, really – but if you insist …): Create a temp table, which is a copy of your ‘test’ table. Add the AutoID identity column to the temp table. Insert the data from test into temp, using an ORDER BY clause. Delete the data from test, and add the identity column. SET IDENTITY_INSERT dbo.test ON INSERT INTO dbo.test
ORDER BY #TEMP.AutoID SET IDENTITY_INSERT dbo.test OFF Things to keep in mind: if your ‘test’ table is referenced by FKs on other tables, you will run into errors.
Also, there is no guarantee that the identity increment will follow the ‘natural’ order of the data in the table, as new records get inserted.
Thanks for the reply! That answers my question. Actually, I didn’t need an ordered identity, I just needed the identity column to match the sort. So all I needed was the first insert to ensure the identity matched the sorted insert. Thanks for all your help!
Just in case you need some more arguments for your superiors. Just tell them to read some beginners material on mathematical set theory. Relational databases, like SQL Server, are based on this theory to a certain extent. Multibags would be more precise, but that’s another story.
Anyway, a set has no meaningful order. And that’s what SQL Server returns without ORDER BY. Another argument for your superiors would be to look up the ANSI SQL 92 standard. Basically that standard says, that sorting of a resultset is implementation defined, however only the ORDER BY is guaranteed to return the set in the way you expect. This is achieved, because ORDER BY is a cursor in disguise. —
Frank Kalis
Microsoft SQL Server MVP
Heute schon gebloggt?http://www.insidesql.de/blogs