CODEWITHSUNDEEP
sqlsql-server
KentZhou
KentZhou

Reputation: 25553

what's order when select data from database?

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?

Upvotes: 3

Views: 1014

Answers (8)

Matt Whitfield
Matt Whitfield

Reputation: 6574

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.

Upvotes: 0

Steve Wortham
Steve Wortham

Reputation: 22220

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.

Upvotes: 0

Jeff Hornby
Jeff Hornby

Reputation: 13640

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.

Upvotes: 1

HLGEM
HLGEM

Reputation: 96572

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.

Upvotes: 6

Keith Adler
Keith Adler

Reputation: 21178

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.

Upvotes: 0

Jacob Mattison
Jacob Mattison

Reputation: 51052

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.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838226

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

Upvotes: 8

John Saunders
John Saunders

Reputation: 161773

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

Upvotes: 4

Related Questions