Developers can change the identity properties of a column if they want to redefine the sequential numbers that are automatically generated and stored in that column when new records are added to the table. They 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. 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. When developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to modify the identity properties for a column, they should follow the next steps:
1. In Object Explorer, right-click the table with columns for which they want to modify the length and click Design (Modify in SP1 or earlier).
The table opens in Table Designer.
2. Clear the Allow nulls checkbox for the column they want to change.
3. In the Column Properties tab, expand the Identity Specification property.
4. Click the grid cell for the Is Identity child property and choose Yes from the drop-down list. If an identity column already exists for the table, setting the Is Identity property on another column resets the property to No on the original column without warning.
5. 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.
6. 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.