SQL Server IDENTITY

2025年3月17日 | 阅读 12 分钟

IDENTITY 关键字是 SQL Server 中的一个属性。当表列定义了 identity 属性时，其值将自动生成递增值。此值由服务器自动创建。因此，我们不能作为用户手动将值输入到 identity 列中。因此，如果我们将列标记为 identity，SQL Server 将以自动递增的方式填充它。

语法

以下是说明 SQL Server 中 IDENTITY 属性用法的语法

以上语法参数解释如下

种子 (Seed)： 它表示加载到表中的行的起始值。默认情况下，其值为 1。
增量 (Increment)： 它表示增量值，该值将添加到最后加载行的 identity 值中。默认情况下，其值为 1。

让我们通过一个简单的例子来理解这个概念。

假设我们有一个“Student”表，我们希望StudentID自动生成。我们有一个起始学生 ID 为 10，并且希望每个新 ID 增加 1。在这种情况下，必须定义以下值。

种子 10

递增 1

CREATE TABLE Student (
 StudentID INT IDENTITY(10, 1) PRIMARY KEY NOT NULL, 
) 

注意：SQL Server 中每个表只允许有一个 identity 列。

SQL Server IDENTITY 示例

让我们了解如何在表中使用 identity 属性。列中的 identity 属性可以在创建新表时或创建后设置。这里我们将通过示例查看两种情况。

新表的 IDENTITY 属性

以下语句将使用 identity 属性在指定的数据库中创建一个新表

CREATE TABLE person (
    PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL,
    Fullname VARCHAR(100) NOT NULL,
    Occupation VARCHAR(50),
    Gender VARCHAR(10) NOT NULL
);

接下来，我们将使用 OUTPUT 子句将新行插入到此表中，以查看自动生成的人员 ID

INSERT INTO person(Fullname, Occupation, Gender)
OUTPUT inserted.PersonID
VALUES('Sara Jackson', 'HR', 'Female');

执行此查询将显示以下输出

此输出显示第一行已在 PersonID 列中插入值 10，如表定义 identity 列中所指定。

让我们向 person 表中插入另一行，如下所示

INSERT INTO person(Fullname, Occupation, Gender)
OUTPUT inserted.*
VALUES('Mark Boucher', 'Cricketer', 'Male'),
('Josh Phillip', 'Writer', 'Male');

此查询将返回以下输出

此输出显示第二行已在 PersonID 列中插入值 11，第三行插入值 12。

现有表的 IDENTITY 属性

我们将通过先删除上述表并创建不带 identity 属性的表来解释这个概念。执行以下语句以删除表

接下来，我们将使用以下查询创建一个表

CREATE TABLE person (
    Fullname VARCHAR(100) NOT NULL,
    Occupation VARCHAR(50),
    Gender VARCHAR(10) NOT NULL
); 

如果要在现有表中添加具有 identity 属性的新列，我们需要使用 ALTER 命令。以下查询将 PersonID 添加为 person 表中的 identity 列

ALTER TABLE person  
ADD PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL; 

显式添加值到 identity 列中

如果我们在上述表中显式指定 identity 列值来添加新行，SQL Server 将抛出错误。请看以下查询

INSERT INTO person(Fullname, Occupation, Gender, PersonID)
VALUES('Mary Smith', 'Business Analyst', 'Female', 13);

执行此查询将抛出以下错误

要显式插入 identity 列值，我们需要先将 IDENTITY_INSERT 值设置为 ON。接下来，执行插入操作以向表中添加新行，然后将 IDENTITY_INSERT 值设置为 OFF。请看以下代码脚本

SET IDENTITY_INSERT person ON

/*INSERT VALUE*/  
INSERT INTO person(Fullname, Occupation, Gender, PersonID)
VALUES('Mary Smith', 'Business Analyst', 'Female', 14);

SET IDENTITY_INSERT person OFF  
   
SELECT * FROM person;

IDENTITY_INSERT ON 允许用户将数据放入 identity 列，而 IDENTITY_INSERT OFF 阻止他们向此列添加值。

执行代码脚本将显示以下输出，我们可以看到 PersonID 值为 14 的行已成功插入。

IDENTITY 函数

SQL Server 提供了一些 identity 函数，用于处理表中的 IDENTITY 列。这些 identity 函数如下所示

@@IDENTITY 函数
SCOPE_IDENTITY() 函数
IDENT_CURRENT 函数
IDENTITY 函数

让我们通过一些例子来看看 IDENTITY 函数。

@@IDENTITY 函数

@@IDENTITY 是一个系统定义的函数，它显示在同一会话中为 IDENTITY 列的表中创建的最后一个 identity 值（最大使用的 identity 值）。此函数列在表中插入新条目后返回由语句生成的 identity 值。当我们执行不创建 IDENTITY 值的查询时，它返回 NULL 值。它始终在当前会话的范围内工作。它不能远程使用。

示例

假设 person 表中当前最大 identity 值为 13。现在我们将在同一会话中添加一条记录，使 identity 值增加 1。然后我们将使用 @@IDENTITY 函数获取在同一会话中创建的最后一个 identity 值。

这是完整的代码脚本

SELECT MAX(PersonID) AS maxidentity FROM person;

INSERT INTO person(Fullname, Occupation, Gender)
VALUES('Brian Lara', 'Cricket', 'Male');

SELECT @@IDENTITY; 

执行脚本将返回以下输出，我们可以看到最大使用的 identity 值为 14。

SCOPE_IDENTITY() 函数

SCOPE_IDENTITY() 是一个系统定义的函数，用于显示当前范围内表中最近的 identity 值。此范围可以是模块、触发器、函数或存储过程。它类似于 @@IDENTITY() 函数，不同之处在于此函数只有一个有限的范围。如果我们在生成同一范围内值的插入操作之前执行 SCOPE_IDENTITY 函数，它将返回 NULL。

示例

以下代码在同一会话中同时使用 @@IDENTITY 和 SCOPE_IDENTITY() 函数。此示例将首先显示最后一个 identity 值，然后向表中插入一行。接下来，它执行这两个 identity 函数。

SELECT MAX(PersonID) AS maxid FROM person;

INSERT INTO person(Fullname, Occupation, Gender)
VALUES('Jennifer Winset', 'Actoress', 'Female');

SELECT SCOPE_IDENTITY();
SELECT @@IDENTITY;

执行代码将在当前会话和相似范围内显示相同的值。请看下面的输出图像

现在我们将通过一个示例看看这两个函数有何不同。首先，我们将使用以下语句创建两个名为 employee_data 和 department 的表

CREATE TABLE employee_data (
 emp_id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, 
 fullname VARCHAR(20) NULL
)
GO
CREATE TABLE department (
 department_id INT IDENTITY(100, 5) PRIMARY KEY, 
 department_name VARCHAR(20) NULL
); 

接下来，我们在 employee_data 表上创建一个 INSERT 触发器。每当我们在 employee_data 表中插入一行时，此触发器都会被调用以在 department 表中插入一行。

以下查询创建一个触发器，用于在每次在 employee_data 表中执行插入查询时，在 department 表中插入默认值 'IT'

CREATE TRIGGER Insert_Department  
ON employee_data  
FOR INSERT AS   
 BEGIN  
  INSERT INTO department  VALUES ('IT')  
 END; 

创建触发器后，我们将向 employee_data 表中插入一条记录，并查看 @@IDENTITY 和 SCOPE_IDENTITY() 函数的输出。

执行查询将向 employee_data 表中添加一行，并在同一会话中生成 identity 值。一旦在 employee_data 表中执行插入查询，它会自动调用触发器以在 department 表中添加一行。employee_data 的 identity 种子值为 1，department 表的 identity 种子值为 100。

最后，我们执行以下语句，其中 SELECT @@IDENTITY 函数显示输出 100，SCOPE_IDENTITY 函数显示输出 1，因为它们仅在同一范围内返回 identity 值。

SELECT MAX(emp_id) FROM employee_data
SELECT MAX(department_id) FROM department

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

这是结果：

IDENT_CURRENT() 函数

IDENT_CURRENT 是一个系统定义的函数，用于显示任何连接下给定表生成的最新 IDENTITY 值。此函数不考虑创建 identity 值的 SQL 查询的范围。此函数需要我们想要获取 identity 值的表名。

示例

我们可以通过先打开两个连接窗口来理解它。我们将在第一个窗口中插入一条记录，该记录在 person 表中生成 identity 值 15。接下来，我们可以在另一个连接窗口中验证此 identity 值，在那里我们可以看到相同的输出。这是完整的代码

1st Connection Window

INSERT INTO person(Fullname, Occupation, Gender)
VALUES('John Doe', 'Engineer', 'Male');
GO
SELECT MAX(PersonID) AS maxid FROM person;

2nd Connection Window

SELECT MAX(PersonID) AS maxid FROM person;
GO
SELECT IDENT_CURRENT('person') AS identity_value;

在两个不同的窗口中执行上述代码将显示相同的 identity 值。

IDENTITY() 函数

IDENTITY() 函数是一个系统定义的函数，用于将 identity 列插入到新表中。此函数与我们用于 CREATE TABLE 和 ALTER TABLE 语句的 IDENTITY 属性不同。我们只能在 SELECT INTO 语句中使用此函数，该语句用于将数据从一个表传输到另一个表。

以下语法说明了此函数在 SQL Server 中的用法

如果源表具有 IDENTITY 列，则使用 SELECT INTO 命令形成的表默认继承它。例如，我们之前创建了一个带有 identity 列的 person 表。假设我们使用带有 IDENTITY() 函数的 SELECT INTO 语句创建一个继承 person 表的新表。在这种情况下，我们将收到错误，因为源表已经有一个 identity 列。请看以下查询

SELECT IDENTITY(INT, 100, 2) AS NEW_ID, 
    PersonID,
    Fullname,
    Occupation,
    Gender 
INTO person_info
FROM person;

执行上述语句将返回以下错误消息

让我们使用以下语句创建一个没有 identity 属性的新表

CREATE TABLE student_data (
 roll_no INT PRIMARY KEY NOT NULL, 
 fullname VARCHAR(20) NULL
)

然后，使用 SELECT INTO 语句（包括 IDENTITY 函数）复制此表，如下所示

SELECT IDENTITY(INT, 100, 1) AS student_id,
  roll_no,
  fullname
INTO temp_data
FROM student_data;

一旦语句执行，我们可以使用 sp_help 命令验证它，该命令显示表属性。

您可以根据指定条件在 TEMPTABLE 属性中看到 IDENTITY 列。

如果我们将此函数与 SELECT 语句一起使用，SQL Server 将抛出以下错误消息

消息 177，级别 15，状态 1，行 2 IDENTITY 函数只能在 SELECT 语句具有 INTO 子句时使用。

重用 IDENTITY 值

我们不能在 SQL Server 表中重用 identity 值。当我们从 identity 列表中删除任何行时，identity 列中将创建一个间隙。此外，当我们向 identity 列中插入新行，并且语句失败或回滚时，SQL Server 将创建一个间隙。间隙表示 identity 值丢失且无法再次生成到 IDENTITY 列中。

考虑以下示例来实际理解它。我们已经有一个包含以下数据的 person 表

接下来，我们将使用以下语句创建两个名为 'position' 和 'person_position' 的表

CREATE TABLE POSITION (
	PositionID INT IDENTITY (1, 1) PRIMARY KEY,
	Position_name VARCHAR (255) NOT NULL
);

CREATE TABLE person_position (
	PersonID INT,
	PositionID INT,
	PRIMARY KEY (PersonID, PositionID),
	FOREIGN KEY (PersonID) REFERENCES person (PersonID),
	FOREIGN KEY (PositionID) REFERENCES POSITION (PositionID)
); 

接下来，我们尝试向 person 表中插入一条新记录，并通过向 person_position 表中添加新行来为其分配职位。我们将通过使用如下所示的事务语句来完成此操作

BEGIN TRANSACTION
    BEGIN TRY
        -- insert a new row into the person table
        INSERT INTO person (Fullname, Occupation, Gender) 
	 VALUES('Joan Smith', 'Manager', 'Male');

        -- assign a position to a new person
        INSERT INTO person_position (PersonID, PositionID)
        VALUES(@@IDENTITY, 10);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0  
            ROLLBACK TRANSACTION;  
    END CATCH

    IF @@TRANCOUNT > 0  
        COMMIT TRANSACTION;

上述事务代码脚本成功执行了第一个插入语句。但第二个语句失败了，因为 position 表中没有 ID 为 10 的职位。因此，整个事务被回滚。

由于 PersonID 列中的最大 identity 值为 16，因此第一个插入语句消耗了 identity 值 17，然后事务被回滚。因此，如果我们在 Person 表中插入下一行，下一个 identity 值将为 18。执行以下语句

INSERT INTO person(Fullname, Occupation, Gender)
VALUES('Peter Drucker',' Writer', 'Female');

再次检查 person 表后，我们看到新添加的记录包含 identity 值 18。

单个表中的两个 IDENTITY 列

从技术上讲，在单个表中创建两个 identity 列是不可能的。如果我们这样做，SQL Server 将抛出错误。请看以下查询

CREATE TABLE TwoIdentityTable (
 ID1 INT IDENTITY (10, 1) NOT NULL,
 ID2 INT IDENTITY (100, 1) NOT NULL
) 

当我们执行此代码时，我们将看到以下错误

但是，我们可以通过使用计算列在单个表中创建两个 identity 列。以下查询创建了一个表，其中包含一个计算列，该列使用原始 identity 列并将其减 1。

CREATE TABLE TwoIdentityTable (
 ID1 INT IDENTITY (10, 1) NOT NULL,
 SecondID AS 10000-ID1,
 Descriptions VARCHAR(60)
)

接下来，我们将使用以下命令向此表中添加一些数据

INSERT INTO TwoIdentityTable (Descriptions)
VALUES ('Javatpoint provides best educational tutorials'),
('www.javatpoint.com')

最后，我们使用 SELECT 语句检查表数据。它返回以下输出

我们可以在图像中看到 SecondID 列如何作为第二个 identity 列，从起始值 9990 减少 10。

SQL Server 的 IDENTITY 列误解

DBA 用户对 SQL Server identity 列存在许多误解。以下是最常见的关于 identity 列的误解列表

IDENTITY 列是唯一的： 根据 SQL Server 的官方文档，identity 属性不能保证列值是唯一的。我们必须使用 PRIMARY KEY、UNIQUE 约束或 UNIQUE 索引来强制列的唯一性。

IDENTITY 列生成连续数字： 官方文档明确指出，在数据库故障或服务器重启时，identity 列中分配的值可能会丢失。这可能导致插入过程中 identity 值出现间隙。当我们从表中删除值或插入语句回滚时，也可能创建间隙。生成间隙的值不能再使用。

IDENTITY 列无法自动生成现有值： identity 列不可能自动生成现有值，除非通过使用 DBCC CHECKIDENT 命令重新设置 identity 属性。它允许我们调整 identity 属性的种子值（行的起始值）。执行此命令后，SQL Server 将不检查表中是否已存在新创建的值。

IDENTITY 列作为 PRIMARY KEY 足以标识行： 如果主键包含表中的 identity 列而没有其他唯一约束，则该列可以存储重复值并阻止列的唯一性。我们知道主键不能存储重复值，但 identity 列可以存储重复值；建议不要在同一列上使用主键和 identity 属性。

插入后使用错误的工具来获取 identity 值： 这也是一个常见的误解，即不了解 @@IDENTITY、SCOPE_IDENTITY()、IDENT_CURRENT 和 IDENTITY() 函数之间获取刚执行的语句直接插入的 identity 值的区别。

SEQUENCE 和 IDENTITY 的区别

我们使用 SEQUENCE 和 IDENTITY 来生成自动编号。但是，它们有一些区别，主要区别在于 identity 依赖于表，而 sequence 不依赖于表。让我们将它们的区别总结成表格形式

IDENTITY	SEQUENCE
identity 属性用于特定表，不能与其他表共享。	DBA 定义的 sequence 对象可以在多个表之间共享，因为它独立于表。
每次在表上执行插入语句时，此属性都会自动生成值。	它使用 NEXT VALUE FOR 子句为 sequence 对象生成下一个值。
SQL Server 不会将 identity 属性的列值重置为其初始值。	SQL Server 可以重置 sequence 对象的值。
我们无法为 identity 属性设置最大值。	我们可以为 sequence 对象设置最大值。
它在 SQL Server 2000 中引入。	它在 SQL Server 2012 中引入。
此属性无法以递减顺序生成 identity 值。	它可以以递减顺序生成值。

结论

本文将全面概述 SQL Server 中的 IDENTITY 属性。在这里，我们学习了 identity 属性如何以及何时使用，它的不同函数，误解，以及它与 sequence 的区别。

下一主题SQL Server CONCAT 函数

SQL Server IDENTITY

语法

注意：SQL Server 中每个表只允许有一个 identity 列。

SQL Server IDENTITY 示例

新表的 IDENTITY 属性

现有表的 IDENTITY 属性

显式添加值到 identity 列中

IDENTITY 函数

@@IDENTITY 函数

SCOPE_IDENTITY() 函数

IDENT_CURRENT() 函数

IDENTITY() 函数

重用 IDENTITY 值

单个表中的两个 IDENTITY 列

SQL Server 的 IDENTITY 列误解

SEQUENCE 和 IDENTITY 的区别

结论

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

SQL Server 教程

数据库操作

SQL Server 数据库

聚合函数

SQL Server 运算符

SQL Server 子句

SQL Server 键

SQL Server 进阶

杂项

服务

面试题

SQL Server IDENTITY

语法

注意：SQL Server 中每个表只允许有一个 identity 列。

SQL Server IDENTITY 示例

新表的 IDENTITY 属性

现有表的 IDENTITY 属性

显式添加值到 identity 列中

IDENTITY 函数

@@IDENTITY 函数

SCOPE_IDENTITY() 函数

IDENT_CURRENT() 函数

IDENTITY() 函数

重用 IDENTITY 值

单个表中的两个 IDENTITY 列

SQL Server 的 IDENTITY 列误解

SEQUENCE 和 IDENTITY 的区别

结论

相关帖子

SQL Server 中的锁

SQL Server JOIN

SQL Server 复制表

SQL Server COALESCE

SQL Server MERGE

SQL Server 重命名列

SQL Server 数学函数

SS DATEDIFF 函数

SQL Server 重命名表

SQL Server 序列

订阅 Tpoint Tech

联系信息

关注我们

教程

面试题

在线编译器