IDENTITY Property

Columns that have the IDENTITY property contain system-generated values that uniquely identify each row within a table. You can use this feature to generate sequential numbers (for example, employee identification numbers). When inserting values into a table with an identity column, SQL Server automatically generates the next identifier based on the last used identity value (incremented by adding rows) and the increment value specified during column creation.

There is no need to remember which column has the IDENTITY property ¾ simply use the IDENTITYCOL keyword instead. When referencing data in a table, use the keyword IDENTITYCOL in place of the identity column name. It can be qualified with a table name if necessary. The IDENTITYCOL keyword can be used in SELECT, INSERT, UPDATE, and DELETE statements to reference an identity column.

Two system functions return identity information for an object containing an identity column. IDENT_SEED returns the seed value specified during creation of an identity column. IDENT_INCR returns the increment value specified during creation of an identity column.

Manipulating Data in Identity Columns

Data inserted into the table should not include a value for an identity column in the INSERT statement; instead, use the DEFAULT VALUES option (available with the INSERT statement).

By default, data cannot be inserted directly into an identity column; however, if a row was accidentally deleted, an identity value can be re-created and reconstructed. To get the last identity value, use the @@IDENTITY global variable. This variable is accurate after an insert into a table with an identity column; however, this value is reset after an insert into a table without an identity column occurs. To allow an insert with a specific identity value, see the SET statement for the IDENTITY_INSERT option.

Important When explicitly inserting values into the identity column, SQL Server does not validate uniqueness or the possibility of a gap based on the explicitly entered value. To ensure that this value is unique, use a UNIQUE or PRIMARY KEY constraint, or create a unique index on the identity column.

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 the IDENTITY_INSERT option ON. If reusing a removed identity value, use the following sample code to check for the next available identity value. Replace tablename, column_type, and max(column_type) - 1 with your table name, identity column datatype, and numeric value of the maximum allowable value (for that datatype) -1.

SET IDENTITY_INSERT tablename ON
go
DECLARE @nextidentval column_type
SELECT @nextidentval = MIN(IDENTITYCOL) IDENT_INCR(tablename)
FROM tablename t1
WHERE IDENTITYCOL BETWEEN IDENT_SEED(tablename) AND max(column_type) - 1
AND NOT EXISTS (SELECT * FROM tablename t2
        WHERE t2.IDENTITYCOL = t1.IDENTITYCOL  IDENT_INCR(tablename))
go
SET IDENTITY_INSERT tablename OFF

To check that no gaps exist, this example uses a smallint identity column on a table named images, where the identity column was defined with a seed of 6 and an increment value of 8:

SET IDENTITY_INSERT images ON
go
DECLARE @@nextidentval smallint
SELECT @@nextidentval = MIN(IDENTITYCOL) IDENT_INCR(images)
FROM images t1
WHERE IDENTITYCOL BETWEEN IDENT_SEED(images) AND 32766
AND NOT EXISTS (SELECT * FROM images t2
        WHERE t2.IDENTITYCOL = t1.IDENTITYCOL  IDENT_INCR(images))
go

Using the IDENTITY Property with SELECT [INTO] or a View

Ordinarily, selecting an existing identity column into a new table or view, the new column inherits the identity property. If one of the following conditions is true, the new column does not inherit the identity property:

• The SELECT statement contains a union, join, group by, or aggregate function
• The identity column is selected more than once
• The identity column is part of an expression

If one of these conditions is true, the column is created NOT NULL instead of inheriting the identity property. Because this column does not have the identity property, a column value must be explicitly specified when inserting a row into the new table.

If none of these conditions is true, the new table will inherit the identity column. All rules and restrictions, for the identity columns apply to the new table.

Permission

CREATE TABLE permission defaults to the database owner, who can transfer it to other users. Any user can create temporary tables.