Identity Key possible problem

Has anyone experience any problems with identity key value not incrementing properly? I have a table with an identity key which should increment by one but has missing value. I would just like to exclude the possiblity of a SQL Server error.

Missing values happen for identity columns when transactions are rolled back.

Each time when you do an insert, the identity value gets incremented, but if the transaction that contains the insert does a rollback, you will loose the value.

You are guaranteed that you will never get the same identity value, but it is possible to get holes in the numbers.

Identity / Auto Incrementing Column

Hi,

I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?

trenyboy wrote:

Hi,

I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?

since int's maximum size is 2G once it's limit is exceeded you will have an arithmetic overflow. same goes with bigint.. although by then 2GB of rows this is a prime candidate for partioning already...