MS SQL Server Indentity Jumps

[Jashan]

Hi All

I have a table in SQL Server with ID having indentity inrement by one.
Table has not any trigger. Frequently ID in the table jumps.

Any help !!!

Thanks

[John Bell]

Hi

If the transaction fails and rolls back the identity value will not be
reused and can therefore jump. Identities are not guaranteed to be
contiguous.

There is an example in Books online on how to fill non-contiguous identity
values.

John

"Jashan" <Ja*******@hotmail.com> wrote in message
news:db**************************@posting.google.c om...

Hi All

I have a table in SQL Server with ID having indentity inrement by one.
Table has not any trigger. Frequently ID in the table jumps.

Any help !!!

Thanks

[Simon Hayes]

Ja*******@hotmail.com (Jashan) wrote in message news:<db**************************@posting.google. com>...

Hi All

I have a table in SQL Server with ID having indentity inrement by one.
Table has not any trigger. Frequently ID in the table jumps.

Any help !!!

Thanks

IDENTITY columns are not guaranteed to maintain a sequence of numbers
with no gaps. The new identity value is assigned before the INSERT is
executed, but if the INSERT fails or is rolled back in a transaction,
then the value will not be reused. Books Online also says that the
value may jump if there are a lot of deletions happening on the table
- see the "IDENTITY (property)" topic.

Another possibility is that someone simply inserted the values you see
with IDENTITY_INSERT, perhaps as part of a bulk load operation.

If you need to ensure that there are no gaps in the values in your
column, then you'll need to code your own solution. You could search
Google for "mssql and sequences" to get some ideas.

Simon