The causes of SQL server 2005 to come back the outcomes of the SELECT inside a different order.

Note: I'm not sure in the event that matters however the query comes with an Order by clause on the non-unique column (Date column to be precise) and also the table is really a temp table.

Edit: I understand the order isn't guaranteed basically wont specify it, however it usually consistent until tips over.

I wish to understand what is "something" that may happen.


If you will find duplicate values within the ORDER BY column, then your order of individuals values is undefined. An order is regardless of the database finds easiest, also it can change anytime unconditionally.

If purchased with a non-unique column, then following the guranteed ordering of this column, an order from the rows inside a given group isn't guranteed - as awm stated, it's lower towards the database. An place can alter an order, joins can alter an order, an update could possibly alter the order.

If you would like guranteed order, then specify some posts inside your order by which will gurantee order.

To become blunt, it truly does not matter why it will change, whether it ever does.

The query engine particularly documents the order is unspecified if you do not really specify it.

Of course, the overall suggestions about depending on undocumented behavior is do not do it!

Explanations why it could change (you will find unquestionably others, why press your luck?):

  • A recompile from the query
  • Stale indexes will get up-to-date
  • Maintenance have operate on the server, repairing indices

You stated

I understand the order isn't guaranteed basically wont specify it, but it always consistent until something happens.

I believe you designed to say

I understand the order isn't guaranteed basically don't specify it, so it certainly is hard to rely on, regardless of how consistent it appears.

Something that affects the query optimizer's choices concerning the execution plan can alter an order of unordered rows.