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.
For the sake of people who haven't seen this error before let me simulate it.
Create table MaxInt
(
sno int identity,
Fname varchar(30)
)
Go
---Inserting my first record
Insert into MaxInt values ('Sample')
/* huh i can't insert billions of records like this and show :) Let me Reseed to last few numbers */
DBCC CHECKIDENT ( MaxInt, ReSeed, 2147483646)
--Try inserting a record. It would
Insert into MaxInt values ('Vadivel')
/* 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 ('Vadivel')
Select * from MaxInt
| Sno | Fname |
| 1 | Sample |
| 2147483647 | Vadivel |
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)
Go
Insert into MaxInt values ('Vadivel')
Insert into MaxInt values ('Vadivel')
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.,
Related article: http://sql-server-performance.com/Community/forums/p/7430/43219.aspx
Comments