What happen in SQL 2005 when it run out of number for an autonumber column?

Question

What happen when SQL Server 2005 happen to reach the maximum for an IDENTITY column? Does it start from the beginning and start refilling the gap?

What is the behavior of SQL Server 2005 when it happen?

Answer 1

You will get an overflow error when the maximum value is reached. If you use the bigint datatype with a maximum value of 9,223,372,036,854,775,807 this will most likely never be the case.

The error message you will get, will look like this:

Msg 220, Level 16, State 2, Line 10
Arithmetic overflow error for data type tinyint, value = 256.

(Source)

As far as I know MS SQL provides no functionality to fill the identity gaps, so you will either have to do this by yourself or change the datatype of the identity column.

In addition to this you can set the start value to the smallest negative number, to get an even bigger range of values to use.

Here is a good blog post about this topic.

Answer 2

It will not fill in the gaps. Instead inserts will fail until you change the definition of the column to either drop the identity and find some other way of filling in the gaps or increase the size (go from int to bigint) or change the type of the data (from int to decimal) so that more identity values are available.