identity and identity increment

Hello,
whenever I add a record to a table with identity column it increments by one
which is what I want. But when I input a record that was reject for any
reason (I.e. primary key violation) and then input another record it will
not be increment by one...it skips the number that would of been assigned
to the incorrect record. is there an easy to ensure that it always
increments by one?> whenever I add a record to a table with identity column it increments by
> one which is what I want. But when I input a record that was reject for
> any reason (I.e. primary key violation) and then input another record it
> will not be increment by one...it skips the number that would of been
> assigned to the incorrect record. is there an easy to ensure that it
> always increments by one?
No, if you are going to use IDENTITY, you should learn to live with gaps, or
else use something more like a sequence generator (essentially you lock the
entire table and use MAX(id_col)+1). If you delete any row other than the
most recent, how do you account for that gap? Do you intend to re-insert a
row with ID = 40 when in fact your next ID should have been 722? Are you
associatng some tangible value to the actual ID value generated? If so,
why? In reality, the consumers of the data should not really care if thir
next row gets an ID of 36 or 37 or 38.
http://www.aspfaq.com/2523
A|||That is the nature of using IDENTITY; transactions that are rolled back caus
e
gaps in the sequence. It's one of the reasons some DBA's won't use IDENTITY.
The only way around it is to use a custom id sequence function. Itzik
Ben-Gan wrote an article for SQL Server Magazine a few months back on how to
create custom identity functions.
HTH,
-Mark Williams
"John Smith" wrote:

> Hello,
> whenever I add a record to a table with identity column it increments by o
ne
> which is what I want. But when I input a record that was reject for any
> reason (I.e. primary key violation) and then input another record it will
> not be increment by one...it skips the number that would of been assigned
> to the incorrect record. is there an easy to ensure that it always
> increments by one?
>
>|||thanks... that helps
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uCh7ueQeGHA.4276@.TK2MSFTNGP03.phx.gbl...
> No, if you are going to use IDENTITY, you should learn to live with gaps,
> or else use something more like a sequence generator (essentially you lock
> the entire table and use MAX(id_col)+1). If you delete any row other than
> the most recent, how do you account for that gap? Do you intend to
> re-insert a row with ID = 40 when in fact your next ID should have been
> 722? Are you associatng some tangible value to the actual ID value
> generated? If so, why? In reality, the consumers of the data should not
> really care if thir next row gets an ID of 36 or 37 or 38.
> http://www.aspfaq.com/2523
> A
>