size of auto identity column

size of auto identity column sets the precision of IDENTITY columns that are automatically created with the sp_dboption auto identity and unique auto_identity index options.

Summary Information

Default value

10

Range of values

1–38

Status

Dynamic

Display level

Intermediate

Required role

System administrator

Configuration group

SQL Server Administration

The maximum value that can be inserted into an IDENTITY column is 10precision -1. After an IDENTITY column reaches its maximum value, all further insert statements return an error that aborts the current transaction.

If you reach the maximum value of an IDENTITY column, you can increase it with a modify operation in the alter table command. See the Transact-SQL Users Guide for examples.

You can also use the create table command to create a table that is identical to the old one, but with a larger precision for the IDENTITY column. After you have created the new table, use the insert command or bcp to copy data from the old table to the new one.