what's order when select data from database?

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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).

Answer 4

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.

Answer 5

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.

Answer 6

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: http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order/1793223#1793223

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

Answer 7

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.

Answer 8

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.