Modify IDENTITY Columns

You can modify the size of an IDENTITY column to increase its range. This might be necessary if either your current range is too small, or the range has been used up because of a server shutdown.

For example, you can increase the range of record_id by entering:

alter table stores
modify record_id numeric(9,0)

You can decrease the range by specifying a smaller precision for the target datatype. If the IDENTITY value in the table is too large for the range of the target IDENTITY column, an arithmetic conversion is raised and alter table aborts the statement.

You cannot add a non-null IDENTITY column to a partitioned table using alter table commands that require a data copy. Data copying is performed in parallel for partitioned tables, and cannot guarantee unique IDENTITY values.