SQL > Advanced SQL > IDENTITY

IDENTITY is a SQL Server-specific command, and its purpose is to allow 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.

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 first row would be 1, and subsequent rows would get a value that is 1 larger than the previous row.

For example, let's say 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 will insert the first value:

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

Now the table has the following values:

Table USER_TABLE

UseridLast_NameFirst_Name
2WashingtonGeorge

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

UseridLast_NameFirst_Name
2WashingtonGeorge
3JeffersonThomas

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

Next: SQL SEQUENCE And NEXTVAL




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy