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屬性為表創建標識列。