Arithmetic overflow error converting identity to data type int

This is a real problem in the project, resulting in the final collapse of all projects! It took two hours to figure out the problem
Background: All of a sudden before I went to work in the morning, I was told that the lights of all the projects could not be turned off normally. Oh, my god, this can’t happen. This has never happened before. Hurry to the company, first with the preparatory plan to all equipment to send instructions to shut down. Then start looking for problems.
Procedure: I initially thought there was a problem with either the server or IIS, because IIS often died at regular intervals and needed to be restarted. But obviously not this time, because the site is accessible. Then check the service. After checking for a long time, I found that there was a code error. It was just a simple statement to update the database.
Update XXX set XX = X where XXXX = XXXX
The following statement returns the Arithmetic overflow error IDENTITY to data type int
Although English is not good, but you can see that the conversion type is wrong, the conversion type int is wrong, the primary key identifies the column, overflow. But I’m sure this statement will be fine, but I still want to have a trigger after this table is updated. So look at the trigger, and there’s a job to insert into another table, and look at that table whose primary key is an int. Suddenly I understood, because the table will store tens of thousands of data a day, although it will be emptied regularly, but the self-growth ID will increase. So id is greater than 200 million, which is 2 to the 32nd power.
Solution: Change the field type to bigint or VARCHar (GUID). Bigint is also limited, but in theory it should be fine!

Read More: