SQL Server标识列

在本教程中，将学习如何使用SQL Server IDENTITY属性向表中添加标识列。

SQL Server IDENTITY列简介

要为表创建标识列，请使用IDENTITY属性，如下所示：

IDENTITY[(seed,increment)]

在上面语法中，

• seed是表中的第一行的值(第一条记录标识列使用的值)。
• increment是添加到上一行标识值的增量值。

seed和increment的默认值是1，即(1,1)。表示加载到表中的第一行的值为：1，第二行的值为：2(在上一行：1的基础上加1)，依此类推。

假设，希望第一行的标识列的值为10，增量值为2，可使用以下语法：

IDENTITY (10,2)

注：第一条记录标识列的值是：10，第二条记录标识列的值是：12，第三条记录标识列的值为：14，依此类推。

请注意，SQL Server允许每个表只有一个标识列。

SQL Server IDENTITY示例

下面创建一个名为hr的新模式用来练习：

CREATE SCHEMA hr;

以下语句在个人标识号(person_id)列上使用IDENTITY属性创建新表：

CREATE TABLE hr.person (
    person_id INT IDENTITY(1,1) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1) NOT NULL
);

首先，向person表中插入一个新行：

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Max','Su', 'M');

输出结果如下：

从输出中可以清楚地看到，第一行在person_id列中加载的值为：1。

接下来，再向person表中插入另一行：

INSERT INTO hr.person(first_name, last_name, gender)
OUTPUT inserted.person_id
VALUES('Mini','Su','F');

输出结果如下：

从输出中可以清楚地看到，第二行在person_id列中的值为：2。

重用标识值

SQL Server不能重用标识值。 如果在标识列中插入行并且执行insert语句失败或回滚，则标识值将丢失，并且不会再次生成。这会导致标识列中出现空白。

看看以下一个示例。

首先，在hr模式中再创建两个表：position和person_position：

CREATE TABLE hr. POSITION (
 position_id INT IDENTITY (1, 1) PRIMARY KEY,
 position_name VARCHAR (255) NOT NULL,

);

CREATE TABLE hr.person_position (
 person_id INT,
 position_id INT,
 PRIMARY KEY (person_id, position_id),
 FOREIGN KEY (person_id) REFERENCES hr.person (person_id),
 FOREIGN KEY (position_id) REFERENCES hr. POSITION (position_id)
);

其次，插入一个新的人员信息并在person_position表中插入一个新记录，为新人员分配一个位置：

BEGIN TRANSACTION
    BEGIN TRY
        -- 插入一个新人员记录
        INSERT INTO hr.person(first_name,last_name, gender)
        VALUES('Max','Su','F');

        -- 分配一个位置
        INSERT INTO hr.person_position(person_id, position_id)
        VALUES(@@IDENTITY, 1);
    END TRY
    BEGIN CATCH
         IF @@TRANCOUNT > 0  
            ROLLBACK TRANSACTION;  
    END CATCH

    IF @@TRANCOUNT > 0  
        COMMIT TRANSACTION;
GO

在此示例中，第一个insert语句已成功执行。 但是，由于在person_position表中没有id值为1的位置，第二个插入语句失败了。 由于失败，整个事务被回滚。

因为第一个INSERT语句使用了三个标识值并且事务被回滚，所以下一个标识值将为:4，如以下语句所示：

INSERT INTO hr.person(first_name,last_name,gender)
OUTPUT inserted.person_id
VALUES('Peter','Drucker','F');

输出结果如下：

在本教程中，学习了如何使用SQL Server IDENTITY属性为表创建标识列。