生成自动值

3 分钟

可能需要为特定表中的某一列自动生成顺序值。 Transact-SQL 提供了两种方法来实现此目的：将 IDENTITY 属性与表中的特定列一起使用，或定义 SEQUENCE 对象并使用该对象生成的值。

IDENTITY 属性

要使用 IDENTITY 属性，请使用小数位数为 0 的数值数据类型定义（表示仅限整数）并包含 IDENTITY 关键字。允许的类型包括所有整数类型和小数类型，其中显式提供小数位数 0。

还可以指定可选种子（起始值）和增量（步长值）。如果将种子和增量留空，则系统会将两者都设置为 1。

注意

指定 IDENTITY 属性，而不是在列定义中指定 NULL 或 NOT NULL。具有 IDENTITY 属性的任何列都自动为非空。可以为自助文档指定 NOT NULL，但如果将列指定为 NULL（表示可以为空），则表创建语句将生成错误。

表中只有一个列可能具有 IDENTITY 属性集；它经常用作主键或备用密钥。

下面的代码演示如何创建在上一节的示例中使用的 Sales.Promotion 表，但这一次使用名为 PromotionID 的标识列作为主键：

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

注意

CREATE TABLE 语句的完整详情超出了本模块的范围。

将数据插入标识列

如果为列定义了 IDENTITY 属性，针对表的 INSERT 语句通常不会为 IDENTITY 列指定值。数据库引擎使用列的下一个可用值生成一个值。

例如，可以在不为 PromotionID 列指定值的情况下，将行插入 Sales.Promotion 表中：

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

请注意，即使 VALUES 子句不包含 PromotionID 列的值，也不需要在 INSERT 子句中指定列清单，IDENTITY 列不需要满足此要求。

如果该行是表中插入的第一行，则结果如下所示：

PromotionID

PromotionName

StartDate

ProductModelID

折扣

说明

Clearance Sale

2021-01-01T00:00:00

0.1

10% discount

创建表时，没有为 IDENTITY 列设置种子值或增量值，因此插入第一行的值为 1。要插入的下一行将被分配 PromotionID 值 2，依此类推。

检索标识值

要在同一会话和作用域内返回最近分配的标识值，请使用 SCOPE_IDENTITY 函数；如下所示：

SELECT SCOPE_IDENTITY();

SCOPE_IDENTITY 函数返回在当前作用域中为任何表生成的最新标识值。如果在特定表中需要最新标识值，则可以使用 IDENT_CURRENT 函数，如下所示：

SELECT IDENT_CURRENT('Sales.Promotion');

重写种子标识值

如果要重写自动生成的值并将特定值分配给 IDENTITY 列，首先需使用 SET IDENTITY INSERT 表名 ON 语句来启用标识插入。启用此选项后，可以为标识列插入显式值，就像插入任何其他列一样。完成后，可以使用 SET IDENTITY INSERT 表名 OFF 语句以继续使用自动标识值，并使用最后一个显式输入的值作为种子。

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

如你所知，IDENTITY 属性用于为表中的列生成值序列。但是，IDENTITY 属性不适合在数据库中多个表之间协调值。例如，假设你的组织区分直销与向经销商的销售，并希望将这些销售的数据存储在单独的表中。这两种销售可能需要唯一的发票号，并且你可能希望避免为两种不同类型的销售复制相同的值。满足此要求的一种解决方案是维护两个表中唯一顺序值的池。

重排标识列

有时，需要重置或跳过列的标识值。为此，你将使用 DBCC CHECKIDENT 函数“重新播种”列。可以使用这种方法跳过许多值，或者在删除表中的所有行后将下一个标识值重置为 1。有关使用 DBCC CHECKIDENT 的完整详细信息，请参阅 Transact-SQL 参考文档。

SEQUENCE

在 Transact-SQL 中，可以使用序列对象来定义独立于特定表的新顺序值。序列对象是使用 CREATE SEQUENCE 语句创建的，可以选择提供数据类型（必须是整数类型、小数或小数位数为 0）、起始值、增量值、最大值以及与性能相关的其他选项。

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

要从序列中检索下一个可用值，请使用 NEXT VALUE FOR 构造，如下所示：

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY 或 SEQUENCE

决定是使用 IDENTITY 列还是 SEQUENCE 对象来自动发布值时，请记住以下几点：

如果应用程序要求在多个表之间或者某个表内的多个列之间共享单个数值系列，请使用 SEQUENCE。
SEQUENCE 允许按另一列对值进行排序。 NEXT VALUE FOR 构造可以使用 OVER 子句来指定排序列。 OVER 子句确保返回的值按照 OVER 子句的 ORDER BY 子句的顺序生成。此功能还可以在 SELECT 中返回行时为行生成行号。在以下示例中，Production.Product 表按照 Name 列进行排序，返回的第一列是序号。
```
SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;
```
即使上一条语句只是选择要显示的 SEQUENCE 值，这些值仍会被用尽，而且显示的 SEQUENCE 值将不再可用。如果运行上述 SELECT 语句多次，则每次都会获取不同的 SEQUENCE 值。
如果你的应用程序需要同时分配多个数字，请使用 SEQUENCE。例如，应用程序需要保留五个序号。如果正在同时向其他进程发出数值，则请求标识值可能会导致在系列中出现间断。可以使用 sp_sequence_get_range 系统过程一次检索序列中的多个数字。
SEQUENCE 可用于更改序列的规范，例如增量值。
IDENTITY 值受保护，不会更新。如果尝试使用 IDENTITY 属性更新列，则会遇到错误。