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:

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:

Upon creation, the table is empty.

We insert the first value:

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:

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.