SQL identity 使用汇总

最新推荐文章于 2021-01-19 22:46:46 发布

忠义白马庙

最新推荐文章于 2021-01-19 22:46:46 发布

阅读量8.3k

点赞数 2

本文详细介绍了 SQL Server 中的身份列功能，包括如何使用 IDENTITY 属性创建自动递增的列，以及如何利用 IDENTITY 函数和 @@IDENTITY 系统函数获取最近插入的标识值。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1）IDENTITY（属性）(Transact-SQL)

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

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

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

语法

IDENTITY [ (seed , increment) ]

参数

seed

加载到表中的第一个行所使用的值。

increment

与前一个加载的行的标识值相加的增量值。

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

注释

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

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

列上的标识属性不确保：

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

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

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

2）

IDENTITY（函数）(Transact-SQL)

只用于在带有 INTO table 子句的 SELECT 语句中将标识列插入到新表中。尽管类似，但是 IDENTITY 函数不是与 CREATE TABLE 和 ALTER TABLE 一起使用的 IDENTITY 属性。

注意
若要创建一个可在多个表中使用的自动递增数字，或者可以从应用程序中调用而不引用任何表的自动递增数字，请参阅序列号。

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

语法

IDENTITY (data_type [ , seed , increment ] ) AS column_name

参数

data_type

标识列的数据类型。标识列的有效数据类型可以是任何整数数据类型类别的数据类型（bit 数据类型除外），也可以是 decimal 数据类型。

seed

要分配给表中第一行的整数值。为每一个后续行分配下一个标识值，该值等于上一个 IDENTITY 值加上 increment 值。如果既没有指定 seed，也没有指定 increment ，那么它们都默认为 1。

increment

要加到表中后续行的 seed 值上的整数值。

column_name

将插入到新表中的列的名称。

返回类型

返回与 data_type 相同的数据类型。

注释

因为该函数在表中创建一个列，所以必须用下列方式中的一种在选择列表中指定该列的名称：

--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable;

--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable;

--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable;

--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable;

3）@@IDENTITY (Transact-SQL)

返回最后插入的标识值的系统函数。

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

语法

@@IDENTITY

返回类型

numeric(38,0)

注释

在一条 INSERT、SELECT INTO 或大容量复制语句完成后，@@IDENTITY 中包含语句生成的最后一个标识值。如果语句未影响任何包含标识列的表，则 @@IDENTITY 返回 NULL。如果插入了多个行，生成了多个标识值，则 @@IDENTITY 将返回最后生成的标识值。如果语句触发了一个或多个触发器，该触发器又执行了生成标识值的插入操作，那么，在语句执行后立即调用 @@IDENTITY 将返回触发器生成的最后一个标识值。如果对包含标识列的表执行插入操作后触发了触发器，并且触发器对另一个没有标识列的表执行了插入操作，则 @@IDENTITY 将返回第一次插入的标识值。出现 INSERT 或 SELECT INTO 语句失败或大容量复制失败，或者事务被回滚的情况时，@@IDENTITY 值不会恢复为以前的设置。

如果语句和事务失败，它们会更改表的当前标识，从而使标识列中的值出现不连贯现象。即使未提交试图向表中插入值的事务，也永远无法回滚标识值。例如，如果因 IGNORE_DUP_KEY 冲突而导致 INSERT 语句失败，表的当前标识值仍然会增加。

@@IDENTITY、SCOPE_IDENTITY 和 IDENT_CURRENT 是相似的函数，因为他们都返回插入到表的 IDENTITY 列的最后一个值。

@@IDENTITY 和 SCOPE_IDENTITY 可以返回当前会话中的所有表中生成的最后一个标识值。但是，SCOPE_IDENTITY 只在当前作用域内返回值，而 @@IDENTITY 不限于特定的作用域。

IDENT_CURRENT 不受作用域和会话的限制，而受限于指定的表。IDENT_CURRENT 可以返回任何会话和任何作用域中为特定表生成的标识值。有关详细信息，请参阅 IDENT_CURRENT (Transact-SQL)。

@@IDENTITY 函数的作用域是执行该函数的本地服务器上的当前会话。此函数不能应用于远程或链接服务器。若要获得其他服务器上的标识值，请在远程服务器或链接服务器上执行存储过程，并使（在远程或链接服务器的环境中执行的）该存储过程收集标识值，并将其返回本地服务器上的发出调用的连接。

复制可能会影响 @@IDENTITY 值，因为该值在复制触发器及存储过程中使用。如果此列是复制项目的一部分，则 @@IDENTITY 不是最近用户创建的标识的可靠指示器。您可以使用 SCOPE_IDENTITY() 函数语法代替 @@IDENTITY。有关详细信息，请参阅 SCOPE_IDENTITY (Transact-SQL)。

注意
必须重新编写调用存储过程或 Transact-SQL 语句才能使用 SCOPE_IDENTITY() 函数，该函数将返回在用户语句作用域内所用的最新标识，而不是复制所用的嵌套触发器作用域内的标识。