How To Set SQL Identity Column (SQL Autonumber) in SQL Server 2005
In this sql tutorial, I'll show how to set identity column in SQL table using SQL Server Management Studio.
SQL identity column is an sql autonumber column which fetches the next integer from an internal counter in SQL Server reserved for that sql table.
SQL Server identity column in a database table is most common way to distinguish each record to get a unique data row in database table.
In general, but not necessarily these SQL Server identity column is set as primary key of the sql table.
SQL Server database administrators or SQL developers may want to set a column as an sql autonumber column during they create table in SQL Server 2005 database.
Especially if they are using the SSMS GUI for administring SQL Server 2005 (Yukon), or any other SQL Server versins like SQL Server 2008, they may want to know how to change column properties to set identity column using SQL Server Management Studio.
Let's assume sql developers create sql table named AutoNumber by running the below t-sql script.
Please note that the Id column is not set as SQL Server identity column during table creation task.
CREATE TABLE AutoNumber (
Id int,
Name nvarchar(25)
)
Later as a SQL Server database administrator, you realized that you need an sql autonumber column or sql identity column in your new table.
Follow the below steps to set sql column as SQL Server identity column to get an sql autonumber function in your database table.
Open SQL Server 2005 Microsoft SQL Server Management Studio (SSMS), connect to Database Engine (SQL Server instance) where you have created your database table.
Drill down till you find the table named AutoNumber (target table where you want to set identity column).
Click the Columns and you will see the list of table columns existing in target sql table.
If you want Id column to be the sql identity column of this table, just open the context menu by right clicking on the column named Id. And select Modify from the context menu.
When you click the Modify menu item, Column Properties dialog screen will open as a tab in the Management Studio shown as below.
On the Column Properties box, set Identity Specification property to "Yes" and keep the Identity Increment and Identity Seed values as default ones.
After you saved your modifications on the sql AutoNumber table, run the below two sql insert statements.
INSERT INTO AutoNumber([Name]) VALUES (N'Kodyaz')
INSERT INTO AutoNumber([Name]) VALUES (N'MSDN')
And after you have insert two records in your sql AutoNumber table, by running the below sql select statement you will see sql autonumber Id field values.
SELECT * FROM AutoNumber
