record sequence?

I add 3 records into a table, the sequence is record 1, 2, and 3. When I use
the Query Analyser to query the table, it shows incorrect sequence, sometime
2,1,3 or sometime 3,2,1. Why not 1,2,3?
Thanks.Tables are not ordered, and rows in a table do not have a sequence. If you
want rows returned in a certain order, you have to specify an ORDER BY
clause. Otherwise SQL Server will assume that the order doesn't matter to
you, and will return the rows in whichever order is the fastest. Which order
is actually the fastest can differ from time to time depending on the server
load, the actual data in the table, and whether parts of the table are
already in memory or have to be read from disk.
Jacco Schalkwijk
SQL Server MVP
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:33A73494-D942-4E15-B8A8-BE986898006C@.microsoft.com...
>I add 3 records into a table, the sequence is record 1, 2, and 3. When I
>use
> the Query Analyser to query the table, it shows incorrect sequence,
> sometime
> 2,1,3 or sometime 3,2,1. Why not 1,2,3?
> Thanks.|||Joe
Specify ORDER BY clause and you get an expected result
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:33A73494-D942-4E15-B8A8-BE986898006C@.microsoft.com...
> I add 3 records into a table, the sequence is record 1, 2, and 3. When I
use
> the Query Analyser to query the table, it shows incorrect sequence,
sometime
> 2,1,3 or sometime 3,2,1. Why not 1,2,3?
> Thanks.|||Unless you use clause ORBER BY in a SELECT statement, sql server does not
guarantee any order of the data selected. You can also create a clustered
index by this column if you will order by it frequently.
AMB
"Joe" wrote:

> I add 3 records into a table, the sequence is record 1, 2, and 3. When I u
se
> the Query Analyser to query the table, it shows incorrect sequence, someti
me
> 2,1,3 or sometime 3,2,1. Why not 1,2,3?
> Thanks.|||The rows of a table have no "ordering." If you want an ordered
result, you need to include an ORDER BY clause in the query,
which allows you to order the rows according to information in
the rows.
If you need to preserve the order in which rows are inserted,
an IDENTITY column or a [datetime] column with default of
GETDATE() are possible ways to do this.
Steve Kass
Drew University
Joe wrote:

>I add 3 records into a table, the sequence is record 1, 2, and 3. When I us
e
>the Query Analyser to query the table, it shows incorrect sequence, sometim
e
>2,1,3 or sometime 3,2,1. Why not 1,2,3?
>Thanks.
>|||Rows in a table do not have any sequences associated with them. Irrespective
of whichever sequence you insert them into a table, they are returned in
some arbitrary order. ( This is because, simply put, relational databases
has its partial basis on set theory and sets by themselves do not associate
ordering of their elements. )
If you are interested in returning the rows in some defined sequence, use
the ORDER BY clause in your SELECT statement.
Anith|||Thanks all.
"Jacco Schalkwijk" wrote:

> Tables are not ordered, and rows in a table do not have a sequence. If you
> want rows returned in a certain order, you have to specify an ORDER BY
> clause. Otherwise SQL Server will assume that the order doesn't matter to
> you, and will return the rows in whichever order is the fastest. Which ord
er
> is actually the fastest can differ from time to time depending on the serv
er
> load, the actual data in the table, and whether parts of the table are
> already in memory or have to be read from disk.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:33A73494-D942-4E15-B8A8-BE986898006C@.microsoft.com...
>
>