3

Suppose I have a table:

CREATE TABLE [tab] (
    [name] varchar,
    [order_by] int
)

There are 10 rows in the table, and all rows have same value for order_by (Let's say it's 0)

If I then issue following SQL:

select * from [tab] order by [order_by]

What's the order of the rows? What factor decides the row order in this case?

John Gietzen
  • 48,783
  • 32
  • 145
  • 190
KentZhou
  • 24,805
  • 41
  • 134
  • 200
  • 1
    @jinsungy: it might be, but it also might not be. It depends on the database, what indexes are present, how large the table is, etc... – Mark Byers Feb 02 '10 at 20:03
  • 2
    Just a tip: don't rely on undefined behaviour. – mpen Feb 02 '10 at 20:04
  • Thanks, guys. everytime when issue Select, I get a same order but I don't know how SQL Server 2008 to decide the order in this case. It is not the order you enter the data. Very strange. – KentZhou Feb 02 '10 at 20:09
  • 2
    The order is not guaranteed without an ORDER BY clause. Among other things, small changes in the query can change the order. It's not strange - it's to be expected. – John Saunders Feb 02 '10 at 20:16
  • 1
    Duplicate: http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order – OMG Ponies Feb 02 '10 at 20:20

8 Answers8

8

It's not defined. The database can spit them out in any order it chooses, and it can even change the order between queries if it feels like it (it probably won't do this, but you shouldn't rely on the order being consistent).

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
6

If your columns that you order by has no variation than there is no guaranteed order.

Any time you want a defined order, you need a good order by clause. I can't even imagine why anyone would use an orderby clause if there is no variation in the column being ordered or why you would even have a column that never has but one value.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
HLGEM
  • 94,695
  • 15
  • 113
  • 186
4

There is no order in this case, since you did not specify an order.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
1

My experience in real life is that when you don't specify any order (or specify one that doesn't actually result in sorting, as in this case) rows generally come out in the order they were added to the table. However, that is in no way guaranteed and I would never rely on it.

Jacob Mattison
  • 50,258
  • 9
  • 107
  • 126
1

Generally speaking you can't depend on the order of records coming out of a table unless you specify an order by clause, and any records with the sames value(s) for the fields in an order by clause will not be sorted.

That being said, there are ways to make an educated guess as to the order of the records that will come out. Usually they will be emitted i the order of the table's clustered index. This is usually the primary key but not always. If there is no clustered index, then it will usually be insert order. Note that you can't depend on either of these things. SQL Server might be doing some optimizations that will change the order.

Jeff Hornby
  • 12,948
  • 4
  • 40
  • 61
0

Typically your table has an identity column with a PKey. If that's the case then that would be the order in SQL Server 2008. Unfortunately, I've experienced older versions of SQL Server tending to give inconsistent results depending on whether you're connecting via OLEDB or ODBC.

Keith Adler
  • 20,880
  • 28
  • 119
  • 189
0

If 'name' was a primary key, then the index would have a specified order (either ASC or DESC). And that's the order that I think you would see in this case. At least that's the behavior I've observed in SQL 2008.

If 'name' had no index then I don't believe the order would be predictable at all.

EDIT:

So even in the situation I described it looks like the order will not necessarily be reliable. There's a better explanation here: SQL best practice to deal with default sort order

I suppose the moral of the story is to specify an order if the order is important to you.

Community
  • 1
  • 1
Steve Wortham
  • 21,740
  • 5
  • 68
  • 90
  • 1
    The order is not guaranteed without an ORDER BY clause. Among other things, small changes in the query can change the order. – John Saunders Feb 02 '10 at 20:09
0

The 'natural' order of rows is the order in which the CLUSTERED index says they are in, and that is the order that rows are generally returned in if you don't specify an order. However, enterprise edition merry-go-round scans mean that you won't always get them in that order, and as a few people have said, you should never rely on that.

If you specify order, and the key you are ordering on is equal for a bunch of rows, then order is not guaranteed at all.

Matt Whitfield
  • 6,436
  • 3
  • 29
  • 44