This article will explore Microsoft SQL server identity
columns, including their problems, use and scope.
Introduction
An Identity column is used in SQL server to create a
surrogate key value for a table. This will be a unique identifier usually in
sequential order. Starting at some predefined number, the Identity column
increments every time a new record is added to the table. For MS Access users,
this is comparable to an Auto Numbering field. For Oracle users, the Identity
column can be thought of as a sequence built into a table.
Creating
The Identity key word is supported in both the create and
alter table statements. The following statement will create a new table with
the product_id column as an identity field.
USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘products’)
DROP TABLE products
GO
CREATE TABLE products
(
product_id int IDENTITY(10,2),
product_name varchar(50)
)
The first number in the Identity function is for the seed
value. The seed will be the first number used as an identity. In our case,
the first value in the table will be 10. The second number is the increment.
The products table id will count up by twos.
To create an identity field from Enterprise Manager, set the
Identity to Yes, and enter a seed and increment. A seed of 1 with an increment
of 1 is the default.
Inserting
Enter three product names into the new table using an insert
into statement. We will only enter the product_name, letting sql create the
identity id.
INSERT INTO products
(product_name)
VALUES
(‘computer’)INSERT INTO products
(product_name)
VALUES
(‘monitor’)INSERT INTO products
(product_name)
VALUES
(‘printer’)
Selecting the new rows out will show our first seed of 10.
Additional product names will increment by two.
By default, values cannot be inserted into an identity
field. The statement:
INSERT INTO products
(product_id, product_name)
VALUES
(18, ‘printer’)
Will fail with error:
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in
table ‘products’ when IDENTITY_INSERT is set to OFF.
To force our value, the IDENTITY_INSERT needs to be set ON:
SET IDENTITY_INSERT products ON INSERT INTO products (product_id, product_name) VALUES (18, 'printer') SELECT * FROM products
The new printer with an id of 18 has been successfully
entered.
We now have a gap in our numbering, from identity 14 to 18.
This will not create any problems for SQL. The next identity used will be 20.
However, if gaps will cause a problem for your particular application, search
BOL for “Use generic syntax for finding gaps in identity values” for detailed
examples of how to find gaps.
