CODEWITHSUNDEEP
databasesql-server-2005select
Gil Stal
Gil Stal

Reputation: 3458

Which situations can cause a select to return a different order

What can cause SQL server 2005 to return the results of a SELECT in a different order.

Note: I don't know if that matters but the query has an Order by clause on a non-unique column (Date column to be specific) and the table is a temp table.

Edit: I know that the order is not guaranteed if I wont specify it, but it usually consistent until something happens.

I want to know what is this "something" that can happen.

Thanks.

Upvotes: 1

Views: 424

Answers (4)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

You said

I know that the order is not guaranteed if I wont specify it, but it usually consistent until something happens.

I think you meant to say

I know that the order is not guaranteed if I don't specify it, so it's always unreliable, no matter how consistent it looks.

Anything that affects the query optimizer's decisions about the execution plan can change the order of unordered rows.

Upvotes: 0

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391346

To be blunt, it really doesn't matter why it should change, if it ever does.

The query engine specifically documents that the order is unspecified if you don't actually specify it.

As always, the general advice on relying on undocumented behavior is don't do it!

Reasons why it might change (there are undoubtedly others, but why press your luck?):

  • A recompile of the query
  • Stale indexes gets updated
  • Maintenance have run on the server, rebuilding indices

Upvotes: 0

Callie J
Callie J

Reputation: 31296

If ordered by a non-unique column, then after the guranteed ordering of that column, the order of the rows within a given group is not guranteed - as awm said, it's down to the database. An insert can change the order, joins can change the order, an update can potentially change the order.

If you want guranteed order, then specify a set of columns in your order by that will gurantee order.

Upvotes: 0

awm
awm

Reputation: 6570

If there are duplicate values in the ORDER BY column, then the order of those values is undefined. The order is whatever the database finds most convenient, and it can change at any time for any reason.

Upvotes: 1

Related Questions