Primary keys

my PKs are out of order. my application relies on the order of these keys.
How do i put back on order ex.
current: desired:
1 1
2 2
5 3
6 4
7 5
Thanks...
drop and recreate the column as PK.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> my PKs are out of order. my application relies on the order of these keys.
> How do i put back on order ex.
> current: desired:
> 1 1
> 2 2
> 5 3
> 6 4
> 7 5
> Thanks...
|||This is a common misunderstanding. A table, by definition, has no order.
You cannot rely on any physical ordering of rows; a clustered index is not a
guarantee. The only safe way to guarantee order in a query is to use an
order by clause.
Beware of small test data sets and simplistic queries; these will appear to
support your invalid assumption.
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
> my PKs are out of order. my application relies on the order of these keys.
> How do i put back on order ex.
> current: desired:
> 1 1
> 2 2
> 5 3
> 6 4
> 7 5
> Thanks...
|||Use ORDER BY when you query the table. A table has no pre-determined order.
All you have done wrong is to fail to specify the order you require.
David Portas
SQL Server MVP
|||"Scott Morris" <bogus@.bogus.com> wrote in message
news:u4def5AzEHA.2036@.TK2MSFTNGP12.phx.gbl...
> This is a common misunderstanding. A table, by definition, has no order.
> You cannot rely on any physical ordering of rows; a clustered index is not
a
> guarantee. The only safe way to guarantee order in a query is to use an
> order by clause.
I thnk what mmc actually means is they are out of sequence.
My guess is he's using an IDENTITY value.

> Beware of small test data sets and simplistic queries; these will appear
to[vbcol=seagreen]
> support your invalid assumption.
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:A7B43C62-8E2A-4100-AD63-09E4B8DB5139@.microsoft.com...
keys.
>
|||As per Greg's suggestion, it looks like you may have an IDENTITY column with
gaps in the sequence (not "out of order" at all). Don't rely on the IDENTITY
column being sequential without gaps. You can't prevent gaps from happening
with IDENTITY so you should change your code to work correctly with this in
mind.
I would also suggest that it's unwise to rely on the sequential nature of
IDENTITY at all. There are situations where this will cause you problems and
the best rule is to assume NOTHING about the IDENTITY sequence and write
your code accordingly. Don't even assume an IDENTITY column will be unique
unless you have a unique constraint on it. Adding a DATETIME column to the
table is a better way to track the order of insertion, if that is your goal.
David Portas
SQL Server MVP
|||Thank you, David.
"David Portas" wrote:

> As per Greg's suggestion, it looks like you may have an IDENTITY column with
> gaps in the sequence (not "out of order" at all). Don't rely on the IDENTITY
> column being sequential without gaps. You can't prevent gaps from happening
> with IDENTITY so you should change your code to work correctly with this in
> mind.
> I would also suggest that it's unwise to rely on the sequential nature of
> IDENTITY at all. There are situations where this will cause you problems and
> the best rule is to assume NOTHING about the IDENTITY sequence and write
> your code accordingly. Don't even assume an IDENTITY column will be unique
> unless you have a unique constraint on it. Adding a DATETIME column to the
> table is a better way to track the order of insertion, if that is your goal.
> --
> David Portas
> SQL Server MVP
> --
>
>