x

Order of the data retrieved

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?

more ▼

asked Oct 19 '09 at 12:36 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 76 79 82

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Oct 19 '09 at 12:49 PM

Kev Riley gravatar image

Kev Riley ♦♦
50.8k 44 49 76

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 19 '09 at 12:50 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 20 '09 at 08:36 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 19 '09 at 12:45 PM

Jorge Segarra gravatar image

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)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977

asked: Oct 19 '09 at 12:36 PM

Seen: 949 times

Last Updated: Oct 19 '09 at 12:36 PM