IDENTITY(属性)(Transact-SQL)

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

备注

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

适用范围:SQL Server(SQL Server 2008 到当前版本)。

主题链接图标 Transact-SQL 语法约定

语法

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;

请参阅

参考

ALTER TABLE (Transact-SQL)

CREATE TABLE (SQL Server)

DBCC CHECKIDENT (Transact-SQL)

IDENT_INCR (Transact-SQL)

@@IDENTITY (Transact-SQL)

IDENTITY(函数)(Transact-SQL)

IDENT_SEED (Transact-SQL)

SELECT (Transact-SQL)

SET IDENTITY_INSERT (Transact-SQL)

概念

复制标识列