在单个 SQL Server 表中拥有两个而非一个标识
在单个 SQL Server 表中拥有两个而非一个标识。
引言
本文将向用户，尤其是 SQL Server 开发人员介绍 SQL Server 的一个限制，即它不允许在一个表中对多个列应用 IDENTITY 属性。在介绍之后，我们将探讨几种解决此限制的方法。在最后一个解决方案，即解决方案 #4 中，我们将探讨如何以一种几乎看似在一个表中创建了两个 IDENTITY 列的方式来解决此限制。
背景
SQL Server 不允许在单个表中创建两个标识列。如果我们遇到需要在单个表中对两个列都要求标识的情况，该怎么办？嗯，有多种方法可以做到这一点。让我们从问题或错误开始，逐一查看...
问题/错误：创建在两个列上具有 IDENTITY 的表
如果用户尝试在 SQL Server 中创建一个在两个列上具有 IDENTITY 属性的表，SQL Server 不允许这样做并返回错误。假设我们要创建一个名为 tblTempTable 的表，该表将有三个列。这些列是：
ID，数据类型为INT。这是主键，具有IDENTITY属性，SEED值为 1，INCREMENT值为 1。ExtraID，数据类型为INT。这是我们要对其拥有IDENTITY属性的第二个列，SEED值为 2，INCREMENT值为 2。TempName，数据类型为VARCHAR(100)。此列将存储相关数据。
创建表的代码是：
CREATE TABLE tblTempTable (
ID INT PRIMARY KEY IDENTITY(1, 1),
ExtraID INT IDENTITY(2, 2), -- NEED TO IMPLEMENT IDENTITY ON THIS COLUMN AS WELL
TempName VARCHAR(100)
);
GO
现在，当我们执行创建表的代码时，SQL Server 返回错误：
Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'tblTempTable'.
Only one identity column per table is allowed.
代码也可在名为“01 - The Error.sql”的 .SQL 文件中找到。
那么，我们该如何解决这个问题呢？显然，我们不能在单个表中创建两个 IDENTITY 列。因此，必须有一个解决方法。
解决方案#1. 在操作前选择标识值
用户可以通过多种方式选择下一个标识值。用户可以简单地在 INSERT 语句之前 SELECT 值，或者用户可以为此目的定义一个用户定义函数。
首先，我们创建表，但合法代码要求我们只有一个列具有 IDENTITY 属性。因此，创建表的代码如下：
CREATE TABLE tblTempTable (
ID INT PRIMARY KEY IDENTITY(1, 1),
ExtraID INT NOT NULL, -- NEED TO IMPLEMENT IDENTITY ON THIS COLUMN
TempName VARCHAR(100)
);
GO
我们还创建了三个变量来为 ExtraID 列创建新的标识。代码如下：
DECLARE @ExtraID AS INT; -- THIS VARIABLE WILL HAVE THE NEW ID
DECLARE @SeedValue AS INT = 0; -- THE SEED VALUE FOR THE IDENTITY
DECLARE @IncrementValue AS INT = 2; -- THE SEED VALUE FOR THE IDENTITY
现在，在执行 INSERT 语句之前，我们将 ExtraID 列的新值 SELECT 到一个变量中，并将该变量传递到 INSERT 语句中。代码如下：
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Aqeel');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Rizwan');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Shariq');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Raheel');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Abu Bakar');
SELECT @ExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue) ) + @IncrementValue FROM tblTempTable;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Waseem');
代码也可在名为“02 - Select before Operation.sql”的 .SQL 文件中找到。
如果软件工程师无法编写可重用的代码，那他/她算什么软件工程师？因此，在下一步中，我们将创建一个用户定义函数来更好地解决这个问题。
解决方案#2. 使用用户定义函数选择标识值
用户定义函数为软件工程师提供了一种创建可重用代码的方法。在这种情况下，用户将创建一个标量函数，该函数将返回 ExtraID 列的新值。创建函数的代码如下：
CREATE FUNCTION dbo.NewTempTableExtraID
(
@SeedValue INT,
@IncrementValue INT
)
RETURNS INT
AS
BEGIN
DECLARE @NewExtraID AS INT;
SELECT @NewExtraID = ISNULL(MAX(ExtraID), _
(@SeedValue - @IncrementValue)) + @IncrementValue FROM tblTempTable;
RETURN @NewExtraID;
END;
GO
请注意，此函数接受两个参数，即 @SeedValue 和 @IncrementValve。这两个参数不言而喻。此函数将选择并返回 ExtraID 列的下一个值。用户定义函数实现了与上一节相同的逻辑。
现在，用户将不再在 INSERT 语句之前选择 ExtraID 列的新值，而是在 INSERT 语句中调用该函数，这将向表中插入新值。
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Aqeel');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Rizwan');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Shariq');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Raheel');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Abu Bakar');
INSERT INTO tblTempTable (ExtraID, TempName) _
VALUES (dbo.NewTempTableExtraID(@SeedValue, @IncrementValue), 'Waseem');
这就是我们如何优化和重用代码，并避免人为错误因素。
代码也可在名为“03 - User Defined Function.sql”的 .SQL 文件中找到。解决方案#3. 创建序列对象
SQL Server 2012 引入了一个名为 SEQUENCE 的新的用户定义模式绑定对象。用户可以创建此对象并配置多个属性，包括 START 值、INCREMENT 值、MINVALUE 和 MAXVALUE（有关更多详细信息，请参见参考文献中的第 1 点）。
现在，我们创建一个 SEQUENCE 对象。创建 SEQUENCE 对象的代码如下：
CREATE SEQUENCE dbo.seqExtraID AS INT START WITH 2 INCREMENT BY 2;
现在我们有了名为 seqExtraID 的 SEQUENCE 对象，我们检索 ExtraID 列的新值并将其传递给 INSERT 语句，将其插入到表中。
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Aqeel');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Rizwan');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Shariq');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Raheel');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Abu Bakar');
SELECT @ExtraID = NEXT VALUE FOR dbo.seqExtraID;
INSERT INTO tblTempTable (ExtraID, TempName) VALUES (@ExtraID, 'Waseem');
您可能会注意到，这与我们在解决方案 #1 中解决问题的方式相同。唯一的区别是，我们不是使用 SELECT 语句选择值，而是从 SEQUENCE 对象中检索值。
最终解决方案来了，它将让我们获得在单个表中拥有两个 IDENTITY 列的相同外观和感觉。
解决方案 #4：表中的 DEFAULT SEQUENCE
我们熟悉 DEFAULT 对象。DEFAULT 对象指定了一个值，当在 insert 过程中没有明确提供值时，该值将被插入到与之绑定的列中（有关更多详细信息，请参见参考文献中的第 2 点）。
因此，首先，我们创建一个 SEQUENCE。代码如下：
CREATE SEQUENCE dbo.seqExtraID AS INT START WITH 2 INCREMENT BY 2;
现在，当我们有了名为 dbo.seqExtraID 的 SEQUENCE 后，我们创建一个表，并使用我们刚刚创建的 SEQUENCE 对象 dbo.seqExtraID 为 ExtraID 列设置默认值！创建表的代码如下：
CREATE TABLE tblTempTable (
ID INT PRIMARY KEY IDENTITY(1, 1),
ExtraID INT NOT NULL DEFAULT _
(NEXT VALUE FOR dbo.seqExtraID), -- NEED TO IMPLEMENT IDENTITY ON THIS COLUMN
TempName VARCHAR(100)
);
请注意，我们如何使用 DETAULT 关键字为 ExtraID 列指定了默认值。一旦表被创建，我们就可以使用简单的 INSERT 语句将数据插入表中。INSERT 语句如下：
INSERT INTO tblTempTable (TempName) VALUES ('Aqeel');
INSERT INTO tblTempTable (TempName) VALUES ('Rizwan');
INSERT INTO tblTempTable (TempName) VALUES ('Shariq');
INSERT INTO tblTempTable (TempName) VALUES ('Raheel');
INSERT INTO tblTempTable (TempName) VALUES ('Abu Bakar');
INSERT INTO tblTempTable (TempName) VALUES ('Waseem');
SELECT * FROM tblTempTable;
当上述语句执行时，SQL Server 将为 SELECT 语句提供结果。结果如下：
您可能会注意到，现在不需要提及 ExtraID 列，也不需要为其提供值。INSERT 语句的执行就像我们在一个表中拥有不止一个，而是两个 IDENTITY 列一样。
关注点
一个表拥有不止一个 IDENTITY 列的幻觉。