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.