IDENTITY (Property) (T-SQL)

Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.


Note The IDENTITY property is not the same as the SQL-DMO Identity property that exposes the row identity property of a column.


Syntax

IDENTITY [(seed, increment )

Arguments
seed
Is the value that is used for the very first row loaded into the table.
increment
Is the incremental value that is added to the identity value of the previous row that was loaded.

You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).

Remarks

If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to ensure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON.

If you are reusing a removed identity value, use the sample code in Example B to check for the next available identity value. Replace tablename, column_type, and max(column_type) - 1 with your table name, identity column data type, and numeric value of the maximum allowable value (for that data type) -1.

Use DBCC CHECKIDENT to check the current identity value and compare it with the maximum value in the identity column.

When the IDENTITY property is used with CREATE TABLE, Microsoft® SQL Server™ uses the NOT FOR REPLICATION option of CREATE TABLE to override the automatic incrementing of an identity column. Usually, SQL Server assigns each new row inserted in a table a value that is some increment greater than the previous highest value. However, if the new rows are replicated from another data source, the identity values must remain exactly as they were at the data source.

Examples
A. Use the IDENTITY property with CREATE TABLE

This example creates a new table using the IDENTITY property for an automatically incrementing identification number.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

        WHERE TABLE_NAME = 'new_employees')

    DROP TABLE new_employees

GO

CREATE TABLE new_employees

(

 id_num int IDENTITY(1,1),

 fname varchar (20),

 minit char(1),

 lname varchar(30)

)

  

INSERT new_employees

    (fname, minit, lname)

VALUES

    ('Karin', 'F', 'Josephs')

  

INSERT new_employees

    (fname, minit, lname)

VALUES

    ('Pirkko', 'O', 'Koskitalo')

  

  

B. Use generic syntax for finding gaps in identity values

This example shows generic syntax for finding gaps in identity values when data is removed.


Note The first part of the following Transact-SQL script is designed for illustration purposes only. You can run the Transact-SQL script that starts with the comment: - - Create the img table.


-- Here is the generic syntax for finding identity value gaps in data.

-- This is the beginning of the illustrative example.

SET IDENTITY_INSERT tablename ON

  

DECLARE @minidentval column_type

DECLARE @nextidentval column_type

SELECT @minidentval = MIN(IDENTITYCOL) FROM tablename

IF @minidentval = IDENT_SEED('tablename')

    SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('tablename')

    FROM tablename t1

    WHERE IDENTITYCOL BETWEEN IDENT_SEED('tablename') AND

        MAX(column_type) AND

        NOT EXISTS (SELECT * FROM tablename t2

            WHERE t2.IDENTITYCOL = t1.IDENTITYCOL +

                IDENT_INCR('tablename'))

ELSE

    SELECT @nextidentval = IDENT_SEED('tablename')

SET IDENTITY_INSERT tablename OFF

-- Here is an example to find gaps in the actual data.

-- The table is called img and has two columns: the first column

-- called id_num, which is an increasing identification number, and the

-- second column called company_name.

-- This the end of the illustration example.

  

-- Create the img table.

-- If the img table already exists, drop it.

-- Create the img table.

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

        WHERE TABLE_NAME = 'img')

    DROP TABLE img

GO

CREATE TABLE img (id_num int IDENTITY(1,1), company_name sysname)

INSERT img(company_name) VALUES ('New Moon Books')

INSERT img(company_name) VALUES ('Lucerne Publishing')

-- SET IDENTITY_INSERT ON and use in img table.

SET IDENTITY_INSERT img ON

  

DECLARE @minidentval smallint

DECLARE @nextidentval smallint

SELECT @minidentval = MIN(IDENTITYCOL) FROM img

 IF @minidentval = IDENT_SEED('img')

     SELECT @nextidentval = MIN(IDENTITYCOL) + IDENT_INCR('img')

     FROM img t1

     WHERE IDENTITYCOL BETWEEN IDENT_SEED('img') AND 32766 AND

        NOT     EXISTS (SELECT * FROM img t2

             WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('img'))

 ELSE

     SELECT @nextidentval = IDENT_SEED('img')

SET IDENTITY_INSERT img OFF

  

See Also
ALTER TABLE @@IDENTITY
CREATE TABLE IDENTITY (Function)
DBCC CHECKIDENT SELECT
IDENT_INCR SET IDENTITY_INSERT
IDENT_SEED  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.