Thursday, August 4, 2011

Arithmetic overflow error converting IDENTITY to data type int

Today I ran into the below error:
Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.
As the error suggests in the table the max limit of INT (2147483647) has reached.
I am creating one example For the people who haven't seen this error before.
Create table MaxInt
  SlNo int identity,
  FirstName varchar(30)
---Inserting my first record
Insert into MaxInt values ('FirstRecord')
/* I Reseed to last few numbers */
DBCC CHECKIDENT ( MaxInt, ReSeed, 2147483646)
--Try inserting a record. It would
Insert into MaxInt values ('LastRecord')
/* Trying to insert another one .... now you would see the error mentioned above as we have reached the max of INT already */
Insert into MaxInt values ('LastRecord')
Select * from MaxInt
At this instance my first aim was to make the app up and running at the earliest and then think of the rest! :)
So i decided to reseed the identity column to -2147483648 and have the values count upward towards zero.
DBCC CHECKIDENT ( MaxInt, ReSeed, -2147483648) 
Insert into MaxInt values ('NewRec')
Insert into MaxInt values ('NewRec')
Now we can insert another 2 Billions records into the table.
[Obviously this is just the idea. Before using it please test against your application code and whether it is fine with having negative values in the table etc.,]
That said, Once we reach the Max limit of INT this is not the only solution we have. Other options which we need to consider are:
1. Convert the column from Int to BigInt datatype 
2. Other version of the given solution is to start the number from 0 and make it count downwards as –1, –2, –3 etc.,

1 comment:

  1. select count(jwmasterforlist.transid) from jwmasterforlist, jwworkorderform

    is giving

    Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.

    i want this problem to be solved so that my programs can work smoothly how to convert the result to bigint for count. i tried all combination but same results are coming that count is not a function. if anyone has a result please send it to which is our development teams email ID. you can visit our website to to know more about our programs which are being executed at a very large scale.

    If any one has any queries then we can clarify the same on it.

    other results do not have much problem because they can be converted. in case of count function it is not so. we have large applications built on this function and cannot be changed.