Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 6, 2004

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM