Renumbering an Identity Column

Is there any way to renumber an identity column?
Due to rows being deleted etc., I have identity columns that go 1, 3, 22, 23
etc.
Is there a way to renumber them 1, 2, 3, 4 etc.?
Hi,
1. Take the data out to a temp table
2. Truncate the original table or use DBCC CHECKIDENT with reseed option to
reset the value back to 1
3. Load the data back to the table. Please do not include the identity
column while insertion. This will generate the
values in order .
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
>
|||"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
As Hari showed, yes there is a way.
Remember this violates the rules of database integrity
|||this implies that you are placing meaning on the numbers.
this is bad design.
(For what it's worth)
Greg Jackson
PDX, Oregon
|||It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> this implies that you are placing meaning on the numbers.
> this is bad design.
> (For what it's worth)
>
> Greg Jackson
> PDX, Oregon
>
|||Greg,
Good points.
But then you have to proliferate the new PK values to all child table foreign keys that refer to this table, yes? Sounds like an operation very susceptible to errors.
Thanks,
Dick
-- Greg Linwood wrote: --
It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> this implies that you are placing meaning on the numbers.
> PDX, Oregon
|||Hi Dick.
Yes - proliferating the keys down to any referencing foreign keys is a
requirement, but this is not susceptible to errors as long as it's planned
well.
If there is any maintenance window, this can be performed as part of regular
maintenance. If not, it needs to be performed transactionally and scheduled
regularly enough that this is not a large burden on the system.
Regards,
Greg Linwood
SQL Server MVP
"Dick" <deacdb2@.hotmail.com> wrote in message
news:8F1D7D0A-5669-4A9E-8434-EA2101D3CE9F@.microsoft.com...
> Greg,
> Good points.
> But then you have to proliferate the new PK values to all child table
foreign keys that refer to this table, yes? Sounds like an operation very
susceptible to errors.
> Thanks,
> Dick
>
> -- Greg Linwood wrote: --
> It doesn't necessarily imply that he's placing meaning on the
numbers. He
> might not even have designed the database!
> Compacting ID columns can be an important maintenance task,
particularly to
> avoid overloading ID columns that use smaller int types, such as
smallint.
> Simple example:
> (1) You've got a table that uses smallint for the identity datatype.
(3rd
> party app & you can't change this)
> (2) You have only 1 row, but they identity value is 32766, so you're
running
> out of space - the ID column will only accept one more row. (This
happens in
> cases where you archive data.)
> (3) You need to compact the data so that you can fit more rows in the
table
> by re-assigning keys to smaller values.
> (4) The only solution in this case is to do what Keith is asking..
> This occurs often in apps that use identity, acquire lots of data and
> perform regular archiving, leaving massive gaps in the ID ranges.
It's also[vbcol=seagreen]
> not only common on SQL Server, it happens on all major DBMS.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...