| Author |
How to reuse autonumber that has been delete?
|
|
|
| I have a field that is set to autonumber (identity type). As records have
been deleted, i found that new inserted records does not reused the number
(autonumber).
Any way to solved it???
--
xxx
| |
| Andrew J. Kelly 2005-11-17, 3:24 am |
| No you can not fill in the gaps. You can reset the next value with
CHECKIDENT. Identity should be used for things like PK's etc. in which case
you should not be concerned with loosing a few values. Otherwise you need
to create your own way to generate ID's.
--
Andrew J. Kelly SQL MVP
"eslim" <eslim@discussions.microsoft.com> wrote in message
news:FAF9CA80-E325-4842-AAB3- 53AC3381C4AA@microso
ft.com...
>I have a field that is set to autonumber (identity type). As records have
> been deleted, i found that new inserted records does not reused the number
> (autonumber).
>
>
> Any way to solved it???
> --
> xxx
| |
| David Portas 2005-11-17, 9:23 am |
| eslim wrote:
> I have a field that is set to autonumber (identity type). As records have
> been deleted, i found that new inserted records does not reused the number
> (autonumber).
>
>
> Any way to solved it???
> --
> xxx
You can't prevent gaps in the IDENTITY column and there's no easy way
to fill them. It doesn't matter though. Use IDENTITY only as a
surrogate key. Avoid assigning any external meaning to it. If you stick
to those rules then gaps won't matter.
--
David Portas
SQL Server MVP
--
| |
| deanKeith 2005-12-17, 9:23 am |
| One additional appraoch you might take is to increment the identity key by 5
or 10 for each new record in case you absolutely must have an intermediate
value, then there is an opportunity to insert that value.
Keith Risman
"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message
news:1132233727.484422.145160@z14g2000cwz.googlegroups.com...
> eslim wrote:
>
> You can't prevent gaps in the IDENTITY column and there's no easy way
> to fill them. It doesn't matter though. Use IDENTITY only as a
> surrogate key. Avoid assigning any external meaning to it. If you stick
> to those rules then gaps won't matter.
>
> --
> David Portas
> SQL Server MVP
> --
>
|
|
|
|