How to: Modify Column Identity Properties
You can change the identity properties of a column if you want to redefine the sequential numbers that are automatically generated and stored in that column when new records are added to the table. You can set the identity properties on only one column per table.
Columns that have the identity property contain system-generated sequential values that uniquely identify each row within a table (for example, employee identification numbers). When inserting values into a table with an identity column, Microsoft SQL Server automatically generates the next identifier based on the last used identity value (the identity seed property) and the increment value (the identity increment property) specified during the creation of the column.
Note
A new version of Table Designer appears for databases in the SQL Server 2012 format. This topic describes the old version of Table Designer, which you use with databases in earlier formats of SQL Server.
In the new version, you can change a table definition through a graphical interface or directly in a script pane. If you use the graphical interface, the table’s definition is automatically updated in the script pane. To apply the SQL code in the script pane, choose the Update button. For more information about the new version, see How to: Create Database Objects Using Table Designer.
Note
The identity property can be set only for a column that disallows null values and whose data type is decimal, int, numeric, smallint, bigint, or tinyint. Also, it cannot be set for a primary key column.
To modify the identity properties for a column
In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition.
The table opens in Table Designer.
Clear the Allow nulls check box for the column you want to change.
In the Column Properties tab, expand the Identity Specification property.
Click the grid cell for the Is Identity child property and choose Yes from the drop-down list.
Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.
Type a value in the Identity Increment cell. This value is the increment that will be added to the Identity Seed for each subsequent row. The value 1 will be assigned by default.
For example, suppose you want to automatically generate a 5-digit Order ID for each row added to the orders table, beginning with 10000 and incremented by a value of 10. To do this, you would type an Identity Seed of 10000, and type an Identity Increment of 10.
If you change any of the identity properties for a table, the existing identity values will be preserved. Your new settings apply only to new rows that are added to the table.
Note
If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If you want to avoid such gaps, do not use the identity property.
The new values of the Identity Specification properties are assigned to the column in Table Designer after you click outside the grid cell or use the TAB key to move to another grid cell. They take effect in the database when you save your changes in Table Designer.