IDENTITY（属性）(Transact-SQL)

项目
03/07/2014

在表中创建一个标识列。此属性与 CREATE TABLE 和 ALTER TABLE Transact-SQL 语句一起使用。

备注

IDENTITY 属性与 SQL-DMO Identity 属性不同，后者提供的是列的行标识属性。

适用范围：SQL Server（SQL Server 2008 到当前版本）。

语法

IDENTITY [ (seed , increment) ]

参数

seed
加载到表中的第一个行所使用的值。
increment
与前一个加载的行的标识值相加的增量值。

必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值 (1,1)。

注释

标识列可用于生成键值。列上的标识属性确保：

基于当前种子和增量生成每个新值。
特定事务的每个新值不同于表上的其他并发事务的新值。

列上的标识属性不确保：

值的唯一性 – 唯一性必须通过使用 PRIMARY KEY 或 UNIQUE 约束或者通过 UNIQUE 索引来实现。
事务内的连续值 – 不保证插入多个行的事务能够为这些行获得连续的值，因为表上可能发生其他并发插入操作。如果值必须是连续的，事务应针对表使用排他锁或使用 SERIALIZABLE 隔离级别。
服务器重新启动或出现其他故障后的连续值 –SQL Server 可能出于性能原因缓存标识值，在数据库故障或服务器重新启动期间一些分配的值可能丢失。这可能导致在插入时标识值之间有间隔。如果不允许有间隔，应用程序应将序列生成器与 NOCACHE 选项结合使用或使用自己的机制来生成键值。
值的重用 – 对于具有特定种子/增量的指定标识属性，引擎不重用标识值。如果特定 insert 语句失败或回滚该 insert 语句，则使用的标识值会丢失，且不会重新生成。这可能导致在生成后续标识值时引入间隔。

这些限制是为了提升性能而在设计中加入的，而且在大多数情形下是可接受的。如果您因为这些限制而不能使用标识值，则可以创建一个包含当前值的独立表，并使用您的应用程序管理对该表的访问和数字分配。

如果发布了包含标识列的表以进行复制，则必须使用与所用复制类型对应的方式来管理标识列。有关详细信息，请参阅复制标识列。

每个表只能创建一个标识列。

示例

A.将 IDENTITY 属性与 CREATE TABLE 一起使用

以下示例将创建一个新表，该表使用 IDENTITY 属性来生成自动递增的标识号。

USE AdventureWorks2012;

IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
   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.使用常规语法查找标识值之间的间隔

以下示例显示了删除了数据时，用于在标识值中查找间隔的常规语法。

备注

以下 Transact-SQL 脚本的第一部分仅供阐释之用。您可以运行以下面的注释开头的 Transact-SQL 脚本：-- Create the img table

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON;
DECLARE @minidentval column_type;
DECLARE @maxidentval column_type;
DECLARE @nextidentval column_type;
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)
    FROM tablename
IF @minidentval = IDENT_SEED('tablename')
   SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
   FROM tablename t1
   WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND 
      @maxidentval AND
      NOT EXISTS (SELECT * FROM tablename t2
         WHERE t2.$IDENTITY = t1.$IDENTITY + 
            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 is the end of the illustration example.

-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL
   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($IDENTITY) FROM img
 IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
    FROM img t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND
      NOT    EXISTS (SELECT * FROM img t2
          WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))
 ELSE
    SELECT @nextidentval = IDENT_SEED('img');
SET IDENTITY_INSERT img OFF;