CREATE TABLE (Transact-SQL) IDENTITY(属性)
适用于:SQL Server
Azure SQL 数据库
azure Synapse Analytics Azure SQL 托管实例
在表中创建一个标识列。 此属性与 CREATE TABLE 和 ALTER TABLE Transact-SQL 语句一起使用。
注意
IDENTITY 属性与 SQL-DMO Identity
属性不同,后者提供的是列的行标识属性。
语法
IDENTITY [ (seed , increment) ]
Azure Synapse Analytics 中的无服务器 SQL 池不支持此语法。
注意
若要查看 SQL Server 2014 (12.x) 及更早版本的 Transact-SQL 语法,请参阅早期版本文档。
参数
seed
加载到表中的第一个行所使用的值。
increment
与前一个加载的行的标识值相加的增量值。
注意
在 Azure Synapse Analytics 中,由于数据仓库的分布式体系结构,标识的值不是增量。 有关详细信息,请参阅使用 IDENTITY 在 Synapse SQL 池中创建代理键。
必须同时指定种子和增量,或者二者都不指定。 如果二者都未指定,则取默认值 (1,1)。
备注
标识列可用于生成键值。 列上的标识属性确保:
每个新值都是基于当前种子和增量而生成。
特定事务的每个新值不同于表上的其他并发事务的新值。
列上的标识属性不确保:
值的唯一性 - 唯一性必须通过
PRIMARY KEY
或UNIQUE
约束或者通过UNIQUE
索引来实现。注意
Azure Synapse Analytics 不支持
PRIMARY KEY
、UNIQUE
约束或UNIQUE
索引。 有关详细信息,请参阅使用 IDENTITY 在 Synapse SQL 池中创建代理键。事务内的连续值 - 不保证插入多个行的事务能够为这些行获得连续的值,因为表上可能发生其他并发插入操作。 如果值必须是连续的,事务应针对表使用排他锁或使用
SERIALIZABLE
隔离级别。服务器重新启动或出现其他故障后的连续值 - SQL Server 可能出于性能原因而缓存标识值,在数据库故障或服务器重新启动期间,一些分配的值可能丢失。 这可能导致在插入时标识值之间有间隔。 如果不允许有间隔,应用程序应使用自己的机制来生成键值。 将序列生成器与
NOCACHE
选项结合使用可以限制从未提交的事务的间隔。值的重用 - 对于具有特定种子/增量的指定标识属性,引擎不重用标识值。 如果特定 insert 语句失败或回滚该 insert 语句,则使用的标识值会丢失,并且不会重新生成。 这可能导致在生成后续标识值时引入间隔。
这些限制是为了提升性能而在设计中加入的,而且在大多数情形下是可接受的。 如果您因为这些限制不能使用标识值,则可以创建一个包含当前值的独立表,并使用您的应用程序管理对该表的访问和数字分配。
如果发布了包含标识列的表以进行复制,则必须使用与所用复制类型对应的方式来管理标识列。 有关详细信息,请参阅复制标识列。
每个表只能创建一个标识列。
在内存优化表中,种子和增量必须分别设置为 1, 1
。 将种子或增量设置为 1
以外的值将导致以下错误:The use of seed and increment values other than 1 is not supported with memory optimized tables
。
在列上设置标识属性后,无法删除该属性。 只要新数据类型与标识属性兼容,就可以更改数据类型。
示例
A. 将 IDENTITY 属性与 CREATE TABLE 一起使用
以下示例将创建一个新表,该表使用 IDENTITY
属性来生成自动递增的标识号。
USE AdventureWorks2022;
GO
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;