Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I've recently been chasing an issue with a client's db... solution found, but impossible to recreate.

Essentially, we're doing a

Select * from mytable where ArbitraryColumn = 75

Where MyTable has an Identity column, called 'MyIndentityColumn' - incremented by one in each insert. Naturally, and normally I would assume that the order returned would be the order in which they are inserted (bad assumption, but one which was forced onto me, through an inherited application - which has been patched).

Essentially, I would like suggestions as to why the database, when restored to my local machine (same OS, same SQL server version - 200 sp3) same collation, and same backup instance restored on it, as a test DB on the client site.

When I perform the above select, I get them in order of insert (i.e. identity column ordered ascending). On the client, it seems random (but the same 'random' order each time)...

A few other points:

  1. I have the same collation on my test server as client
  2. Same DB backup restored to a test only I can access
  3. Same SQL server version and service pack
  4. Same OS
  5. Test DB is a new DB - new log and MDF...

I have the problem 'solved' by adding an explicit order by clause but I want to undertand the cause of the issue, given the exact nature of my attempts to recreate it beuing futile, and perfectly recreatable on the client server...

Thanks in advance,

Dave

share|improve this question
    
I don't have an answer, but check out this other ServerFault question that discusses backup/restore operations at the physical level. Basically, the backup/restore doesn't change the order your data or indexes are laid out on disk, so something else has to be going on. serverfault.com/questions/33432/… –  MattB May 21 '10 at 15:29
    
belongs on StackOverflow because it's a coding question –  gbn May 21 '10 at 18:22
1  
Without an ORDER BY clause there is no default order. Any order is valid. The records may or may not be stored in a particular order within the database. The database may even be reorganized to a different order during maintenance. –  Nate May 21 '10 at 18:32

3 Answers 3

up vote 2 down vote accepted

Ordering of a record set is determined only by the outermost ORDER BY.

For avoidance of doubt: there is no default sort order

The "reproducible" order you mentioned have only reflects the how SQL Server reads the data in the execution plan for your server at that time. Same service pack? Same edition? Same number of CPUs (including HT)? Exactly the same SET options?

If the plan changes, the order will probably change too. It had nothing to do with insert order or index order or disk location order.

This question belongs on StackOverflow and has been answered before:

From MSDN, Sorting Rows with ORDER BY

ORDER BY guarantees a sorted result only for the outermost SELECT statement of a query. For example, consider the following view definition

From Conor's blog ,

Does the SQL Server Query Optimizer consider index fragmentation in plan selection?

No, it does not directly care.

share|improve this answer
    
Further to your comment on my response... I think we are saying the same thing. The plan doesn't care about index fragmentation, so therefore it returns records as it finds them and in doing so follows the fragmentation. So if you insert a bunch of rows in a table and then read them out using an identity column that is your clustered index, then they will come out in the same order. If you insert/delete/change things over time, the index will become fragmented and the returned dataset follows that fragmentation. –  Scott Lundberg May 22 '10 at 3:19
    
I know there is no default sort order, and I've already said I used order by. I know nothing should be assumed. It is 'reproducible'. Their must be a difference that causes this behaviour, and this is what I'm trying to understand. I've stated in the post same service pack, and the edition is also correct. I also know how an ORDER BY works, and as I've said in my post. What I'm trying to pin down, is why the same db instance, same service pack, same edition, same collation's etc give different behaviour. –  Dave May 23 '10 at 22:51

The order in which the records are returned depends on your indexing. Three things come into play:

  • what is the index being used in the query and is your identity column the main part of the index.
  • How fragmented the index being used is. If it is highly fragmented, then the records will most certainly be returned out of order.
  • Is the index you are using the clustered index on that table

The only way to guarantee the order of records returned is to use an ORDER BY statement. If you are depending on the specific order of the returned recordset, then you must use an ORDER BY statement.

share|improve this answer
    
This is not true. How SQL Server reads in the data as part of the plan determines the "random" order. The engine does not account for index fragmentation –  gbn May 21 '10 at 18:16
    
I don't agree. How it finds the data depends directly on the clustered index. If it can't find the data, then it can't return it in a select statement. The plan is related because of the way it joins to other tables and uses those indexes in the query, but if you did SELECT * FROM Table1, the plan consists of one entry: scan Table1. That scan will follow the clustered index. Here is one article explaining my position. sqlservercentral.com/articles/Indexing/sqlserver2000indexing/… –  Scott Lundberg May 22 '10 at 3:14
    
And if it's a parallel plan? –  gbn May 22 '10 at 4:59
    
The OP was talking about a single table. That is and should be the context of our discussion. But to answer your question, I don't see why a parallel plan is any different... It's still going to do a table scan based on the best index or the index specified by the query author. Do you see it differently? Also, if you subscribe to SQL Server Central's newletter, there is very appropriate article titled "Clustered Indexes? Sedimentary, my Dear Watson". They haven't posted it to the site yet, but it will link it when they do. –  Scott Lundberg May 22 '10 at 15:16
    
Another link with the same discussion: rhinocerus.net/forum/soft-sys-sas/… "it's going to return them in the order that the rows appear in the database, not the index. You will also get inconsistent returned data even with a clustered index-- unless you have performed a reorg of the database immediately before running your pull, you will have rows out of order due to the way that they're physically stored in the data (when you insert a new row, it goes at the end of the cluster closest to where it appears in the index)." –  Scott Lundberg May 22 '10 at 15:23

After some extensive investigation, we found the route problem...

The client had an additional index on a table, which was throwing everythign out of sync, but only on their server. When tested locally, either a backup of the LDF/MDF or a restore onto a fresh database - the problem wasn't exhibited.

On the client machine, on the original mdf/mdf, after being backed up and restored, or on a fresh database which was restored onto, the issue is exhibited 100% reliably.

As proof that the index was the cause, we could remove and re-add the index and see the unwanted behaviour happening and then not happening.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.