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, 2009 at 12:36 PM in Default

avatar image

Steve Jones - Editor ♦♦
5.1k 79 93 87

(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, 2009 at 12:49 PM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

(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, 2009 at 12:50 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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, 2009 at 08:36 AM

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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, 2009 at 12:45 PM

avatar image

Jorge Segarra
419 2 4

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

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:

x1069

asked: Oct 19, 2009 at 12:36 PM

Seen: 1266 times

Last Updated: Oct 19, 2009 at 12:36 PM

Copyright 2016 Redgate Software. Privacy Policy