How to Set SQL Identity Column in SQL Server 2005

Updated on 06 Jan 2012,
Published on 20 Feb 2009

SQL Identity Column in SQL Server 2005 enables you to create a column in database table that could auto generate an integer value for each record inserted into the table. This auto incrementing feature of column enables it generate unique value for each row of a table that can also be used as Primary key like we discussed in the previous tutorial about SQL Table Primary Key. Here we will learn how to set SQL Identity Column in SQL Server 2005 table using SQL Server Management Studio Express Interface. SSMSE interface provides a feature that shows column properties at the bottom while designing a database table. In the column properties window you see all the associated properties of the selected column based on its data type. First you need to open the table in design view. Right click on table name in the left side Object Explorer under the Databases node DatabaseName TableName and then click on Design. This will open the table in design view. Next you can set the SQL Identity column very easily in 2 steps:

1. Select the column in design view of SQL Table (Design view in SSMSE). After selecting it will display the associated properties of that column at the bottom (properties window). Scroll down the property window to locate the Identity Specification property of integer type column. Yes do remember that Identity Specification property appears in enabled state only for numeric fields such as bigint, decimal, int, numeric, smallInt, tinyInt data types.

2. In the properties window double click on Identity Specification property group to expand it. Then set the (Is Identity) property as Yes. Changing the value from No to Yes for (Is Identity) property (as shown in the figure 1) will enable the other two properties under Identity Specification group. These are Identity Increment and Identity Seed. Identity Increment accepts integer type value (by default 1) that increments the identity value each time by specified number. For example if you will set it as 2 then Identity column will insert the values as 2, 4, and 6 so on. Identity Seed also accepts integer type value (by default 1) that sets the starting point for the Identity column. For example if its value is 0 then it will insert the identity column value equal to 0 for first row in the table, and if its value is 1000 then it will insert identity column value equal to 1000 for first row.

Continue to next tutorial: Create SQL Table Foreign Key using SSMSE 2005 to learn how to set relationship key between different tables in SQL Server.

3 Responses to "How to Set SQL Identity Column in SQL Server 2005"
Madhu
Thank you so much for contribution
piyusha
This was really helpfull..... Thanks
Yannick
How can this command be done manually via a sql query command?
Leave a Comment
* required
* required
* will not be published
* optional
* hint: http://www.example.com
  • Subscribe via Email