Getting the Wrong Identity in Microsoft SQL Server identity Columns?

By Don Schlichting

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.

More..
http://www.databasejournal.com/features/mssql/article.php/3307541