Transact-SQL Topics: An Identity Column

	An Identity Column

Description

An identity column is one whose value is automatically created by the database engine when a new record is added. This makes sure that each record has a unique value for that field.

To visually create an identity column, display the table in Design View. In the top section, specify the name of the column. Set its data type to an integer-based type. Usually, the data type used is int.

In the bottom section, click and expand the Identity Specification property. The first action you should take is to set its (Is Identity) property from No to Yes.

Once you have set the value of the (Is Identity) property to Yes, the first time the user performs data entry, the value of the first record would be set to 1. This characteristic is controlled by the Identity Seed property. If you want the count to start to a value other than 1, specify it on this property.

After the (Is Identity) property has been set to Yes, the SQL interpreter would increment the value of each new record by 1, which is the default. This means that the first record would have a value of 1, the second would have a value of 2, and so on. This aspect is controlled by the Identity Increment property. If you want to increment by more than that, you can change the value of the Identity Increment property.

To create an identity column in Transact-SQL, after the name and data type of the column, type identity followed by parentheses. Between the parentheses, enter the seed value, followed by a comma, followed by the increment value. Here is an example:

CREATE TABLE StoreItems(
    ItemID int IDENTITY(1, 1) NOT NULL, 
    Category nvarchar(50),
    ItemName nvarchar(100) NOT NULL,
    Size nvarchar(20),
    UnitPrice money);
GO