Order of the data retrieved

Question

I have created a table and inserted data in it:

create table States
( StateID int
  , StateName varchar(20)
)

insert States select 1, 'Alaska'
insert states select 2, 'Delaware'
insert states select 3, 'Hawaii'
insert states select 4, 'Washington'

When I select from the table, will I always get this data in the order it was inserted in?

Answer 1

0

No!

You MAY get it in the order it was inserted, but there is no guarantee (even with a clustered index).

You must specifiy an ORDER BY clause, to be certain of the order of data returned.

answered Oct 19 '09 at 12:49 PM

Kev Riley ♦♦
46k ● 38 ● 43 ● 69

(comments are locked)

Answer 2

0

It's never guaranteed. Even if you have a clustered index on the table, then you may well find that it works fine until one day your DB is installed on an enterprise edition server that decides to do a merry-go-round scan. See the 'advanced scanning' section on this page.

answered Oct 19 '09 at 12:50 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

Answer 3

0

Will you "always" get the data back in that order, NO. If you have a clustered index that is predicated on the order in which the data is inserted will you "usually" get the data back in that order, yes. But the difference between usually and always is pretty vast. I wouldn't count on it, at all. Use an ORDER BY clause to ensure the order of the data returned.

answered Oct 20 '09 at 08:36 AM

Grant Fritchey ♦♦
61.5k ● 12 ● 19 ● 66

(comments are locked)

Answer 4

0

It Depends (YESSS!!!!<--FYI, Yes isn't the answer, I'm celebrating use of It Depends)

If there's a clustered index on the table the order of the data returned will come back according to which field is indexed.

answered Oct 19 '09 at 12:45 PM

Jorge Segarra
419 ● 2

The order of data returned by a query will not necessarily match the clustered index. The only way to guarantee that is to use ORDER BY.

Dec 17 '09 at 05:25 PM David 1

(comments are locked)

Order of the data retrieved

Follow this question

Related Questions