CREATE TABLE (Transact-SQL) IDENTITY(属性)CREATE TABLE (Transact-SQL) IDENTITY (Property)

适用于: 是SQL Server是Azure SQL 数据库是Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

在表中创建一个标识列。Creates an identity column in a table. 此属性与 CREATE TABLE 和 ALTER TABLE Transact-SQLTransact-SQL 语句一起使用。This property is used with the CREATE TABLE and ALTER TABLE Transact-SQLTransact-SQL statements.

备注

IDENTITY 属性与 SQL-DMO Identity 属性不同,后者提供的是列的行标识属性。The IDENTITY property is different from the SQL-DMO Identity property that exposes the row identity property of a column.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
IDENTITY [ (seed , increment) ]  

参数Arguments

seed seed
加载到表中的第一个行所使用的值。Is the value that is used for the very first row loaded into the table.

increment 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. 如果二者都未指定,则取默认值 (1,1)。If neither is specified, the default is (1,1).

RemarksRemarks

标识列可用于生成键值。Identity columns can be used for generating key values. 列上的标识属性确保:The identity property on a column guarantees the following:

  • 每个新值都基于当前种子和增量而生成。Each new value is generated based on the current seed & increment.

  • 特定事务的每个新值不同于表上的其他并发事务的新值。Each new value for a particular transaction is different from other concurrent transactions on the table.

列上的标识属性不确保:The identity property on a column does not guarantee the following:

  • 值的唯一性 - 唯一性必须通过“PRIMARY KEY”或“UNIQUE”约束或者通过“UNIQUE”索引来实现 。Uniqueness of the value - Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

  • 事务内的连续值 - 不保证插入多个行的事务能够为这些行获得连续的值,因为表上可能发生其他并发插入操作。Consecutive values within a transaction - A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. 如果值必须是连续的,事务应针对表使用排他锁或使用 SERIALIZABLE 隔离级别。If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

  • 服务器重新启动或出现其他故障后的连续值 -SQL ServerSQL Server - 可能出于性能原因而缓存标识值,在数据库故障或服务器重新启动期间,一些分配的值可能丢失。Consecutive values after server restart or other failures -SQL ServerSQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. 这可能导致在插入时标识值之间有间隔。This can result in gaps in the identity value upon insert. 如果不允许有间隔,应用程序应使用自己的机制来生成键值。If gaps are not acceptable then the application should use its own mechanism to generate key values. 将序列生成器与 NOCACHE 选项结合使用可以限制从未提交的事务的间隔。Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

  • 值的重用 - 对于具有特定种子/增量的指定标识属性,引擎不重用标识值。Reuse of values - For a given identity property with specific seed/increment, the identity values are not reused by the engine. 如果特定 insert 语句失败或回滚该 insert 语句,则使用的标识值会丢失,且不会重新生成。If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. 这可能导致在生成后续标识值时引入间隔。This can result in gaps when the subsequent identity values are generated.

这些限制是为了提升性能而在设计中加入的,而且在大多数情形下是可接受的。These restrictions are part of the design in order to improve performance, and because they are acceptable in many common situations. 如果您因为这些限制而不能使用标识值,则可以创建一个包含当前值的独立表,并使用您的应用程序管理对该表的访问和数字分配。If you cannot use identity values because of these restrictions, create a separate table holding a current value and manage access to the table and number assignment with your application.

如果发布了包含标识列的表以进行复制,则必须使用与所用复制类型对应的方式来管理标识列。If a table with an identity column is published for replication, the identity column must be managed in a way that is appropriate for the type of replication used. 有关详细信息,请参阅复制标识列For more information, see Replicate Identity Columns.

每个表只能创建一个标识列。Only one identity column can be created per table.

在内存优化表中,种子和增量必须分别设置为 1、1。In memory-optimized tables the seed and increment must be set to 1,1. 将种子或增量设置为 1 以外的值将导致以下错误:内存优化表不支持使用 1 以外的种子和增量值。Setting the seed or increment to a value other than 1 results in the following error: The use of seed and increment values other than 1 is not supported with memory optimized tables.

示例Examples

A.A. 将 IDENTITY 属性与 CREATE TABLE 一起使用Using the IDENTITY property with CREATE TABLE

以下示例将创建一个新表,该表使用 IDENTITY 属性来生成自动递增的标识号。The following example creates a new table using the IDENTITY property for an automatically incrementing identification number.

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.B. 使用常规语法查找标识值之间的间隔Using generic syntax for finding gaps in identity values

以下示例显示了删除了数据时,用于在标识值中查找间隔的常规语法。The following example shows generic syntax for finding gaps in identity values when data is removed.

备注

以下 Transact-SQLTransact-SQL 脚本的第一部分仅供阐释之用。The first part of the following Transact-SQLTransact-SQL script is designed for illustration only. 您可以运行以下面的注释开头的 Transact-SQLTransact-SQL 脚本:-- Create the img tableYou can run the Transact-SQLTransact-SQL script that starts with the comment: -- 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;  

另请参阅See Also

ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL) CREATE TABLE (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL) DBCC CHECKIDENT (Transact-SQL)
IDENT_INCR (Transact-SQL) IDENT_INCR (Transact-SQL)
@@IDENTITY (Transact-SQL) @@IDENTITY (Transact-SQL)
IDENTITY(函数)(Transact-SQL) IDENTITY (Function) (Transact-SQL)
IDENT_SEED (Transact-SQL) IDENT_SEED (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
SET IDENTITY_INSERT (Transact-SQL) SET IDENTITY_INSERT (Transact-SQL)
复制标识列Replicate Identity Columns