SQL IDENTITY

IDENTITY is used in Microsoft SQL Server to automatically insert numerical primary key values to a table as new data is inserted. This is similar to the AUTO INCREMENT command in MySQL.

Syntax

The syntax for IDENTITY is as follows:

CREATE TABLE TABLE_NAME
(PRIMARY_KEY_COLUMN INT PRIMARY KEY IDENTITY ( [Initial_Value], [Interval] ),
...);

where [Initial_Value] is the first value of the primary key, and [Interval] is the interval between two consecutive identity values. If no [Initial_Value] or [Interval] is specified, the default for both is 1. In other words, the primary key for the first row would be 1, and subsequent rows would get a primary key value that is 1 larger than the previous row.

Example

Assume we want to create a table that consists of a primary key, last name, and first name. We use the following SQL:

CREATE TABLE USER_TABLE
(Userid int PRIMARY KEY IDENTITY(2,1),
 Last_Name nvarchar(50),
 First_Name nvarchar(50));

Upon creation, the table is empty.

We insert the first value:

INSERT INTO USER_TABLE VALUES ('Washington', 'George');

Now the table has the following values:

Table USER_TABLE

Userid is 2 because we had specified the initial value to be 2.

Next we insert the second value:

INSERT INTO USER_TABLE VALUES ('Jefferson', 'Thomas');

Now the table has the following values:

Table USER_TABLE

userid for the second row is 3 because it is 1 larger than the previous Userid, which is 2.