Quick one today:

Earlier, we ran into an interesting error in our lab environment.

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

Initially, this error did not make sense. There are no calculations performed on this column data for it to overflow, but then it dawned on us. Its the INT vs. BIGINT issue. The column has INT as the datatype and it has reached its maximum limit: 2,147,483,647. After that, not a single record could get into the table.

Immediately, changed the column datatype to BIGINT to let the application processes continue to pump data into the table. Surprisingly, there were not errors from the .Net application from the datatype change.

One interesting point that came up during all of this was, the time it took to change from INT to BIGINT. It was completed in under a few seconds (around 30 seconds). For a table this big, the initial guesstimate was a few minutes, but that was proven completely wrong. When we recently, performed similar task on a VARCHAR column (on a large table), it took more than an hour. INT is 4 bytes, BIGINT is 8 bytes. So, if we increase a VARCHAR column by 4 bytes, it should also, in theory, take the same time, right?

In our lab, to be sure, I ran a few tests to measure the difference. And the difference is consistent. Converting INT to BIGINT takes 90 seconds, but a CHAR change from 10 to 14 takes 180 seconds. Any thoughts?

Hope this helps,
_Sqltimes