CREATE TABLE (Transact-SQL) IDENTITY (屬性)

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse Analytics

建立資料表中的識別欄位。 這個屬性會搭配 CREATE TABLE 和 ALTER TABLE Transact-SQL 陳述式使用。

注意

IDENTITY 屬性與公開數據行之數據列識別屬性的 SQL-DMO Identity 屬性不同。

Transact-SQL 語法慣例

Syntax

IDENTITY [ (seed , increment) ]

Azure Synapse Analytics 的無伺服器 SQL 集區不支援此語法。

注意

若要檢視 SQL Server 2014 （12.x） 和舊版的 Transact-SQL 語法，請參閱 舊版檔。

引數

seed

用於載入資料表中第一個數據列的值。

increment

加入至已載入上一個數據列之識別值的累加值。

注意

在 Azure Synapse Analytics 中，身分識別的值不會因為數據倉儲的分散式架構而累加。 如需詳細資訊，請參閱 在 Synapse SQL 集區中使用 IDENTITY 建立代理密鑰。

您必須同時指定種子和遞增，或同時不指定這兩者。 如果同時不指定這兩者，預設值便是 (1,1)。

備註

識別欄位可用於產生索引鍵值。 資料行上的 identity 屬性保證下列條件：

  • 每個新值都會根據目前的種子和增量產生。

  • 特定交易的每個新值都與資料表上的其他並行交易不同。

資料行上的識別屬性不保證下列條件：

  • 值的唯一性 - 唯一性必須使用 或 條件約束或UNIQUEUNIQUE索引來強制執行唯一PRIMARY KEY性。

    注意

    Azure Synapse Analytics 不支援 PRIMARY KEY 或 UNIQUE 條件約束或 UNIQUE 索引。 如需詳細資訊，請參閱 在 Synapse SQL 集區中使用 IDENTITY 建立代理密鑰。

  • 交易 中的連續值 - 插入多個數據列的交易不保證會取得數據列的連續值，因為數據表上可能會發生其他並行插入。 如果值必須連續，則交易應該在數據表上使用獨佔鎖定或使用 SERIALIZABLE 隔離等級。

  • 重新啟動伺服器或其他失敗之後的連續值 - SQL Server 可能會基於效能考量而快取識別值，因此在資料庫失敗或伺服器重新啟動期間，某些指派的值可能會遺失。 這可能會導致插入識別值之後的間隙。 如果無法接受差距，則應用程式應該使用自己的機制來產生索引鍵值。 使用具有 選項的 NOCACHE 序列產生器，可以將間距限制為永遠不會認可的交易。

  • 重複使用值 - 對於具有特定種子/增量的指定識別屬性，引擎不會重複使用識別值。 如果特定的 insert 語句失敗，或如果 insert 語句回復，則會遺失已取用的識別值，且不會再次產生。 這樣在產生後續識別值時會產生間隙。

這些限制是設計中改善效能的一部分，而且在許多常見情況下都是可接受的。 如果您因為這些限制而無法使用身分識別值，請建立一個保存目前值的個別數據表，並管理對您應用程式的數據表和數位指派的存取權。

如果發行含識別欄位的資料表來進行複寫，您必須依照使用的複寫類型所適用的方式，來管理識別欄位。 如需詳細資訊，請參閱複寫識別資料欄。

每份資料表都只能建立一個識別欄位。

在記憶體優化資料表中，種子和增量必須設定為 1, 1。 將種子或遞增設定為以外的 1 值，會產生下列錯誤： The use of seed and increment values other than 1 is not supported with memory optimized tables。

在數據行上設定身分識別屬性之後，就無法移除該屬性。 只要新的數據類型與identity屬性相容，就可以變更數據類型。

範例

A. 搭配 CREATE TABLE 使用 IDENTITY 屬性

下列範例會利用自動累加的識別碼之 IDENTITY 屬性，來建立一份新的資料表。

USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.new_employees', 'U') IS NOT NULL
    DROP TABLE new_employees;
GO

CREATE TABLE new_employees (
    id_num INT IDENTITY(1, 1),
    fname VARCHAR(20),
    minit CHAR(1),
    lname VARCHAR(30)
);

INSERT new_employees (fname, minit, lname)
VALUES ('Karin', 'F', 'Josephs');

INSERT new_employees (fname, minit, lname)
VALUES ('Pirkko', 'O', 'Koskitalo');

B. 使用泛型語法來尋找識別值中的間距

下列範例會顯示在移除資料時，用來尋找識別值間距的一般語法。

注意

下列 Transact-SQL 指令碼的第一部份僅供解說之用。 您可以執行開頭是下列註解的 Transact-SQL 指令碼：-- Create the img table。

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON;

DECLARE @minidentval column_type;
DECLARE @maxidentval column_type;
DECLARE @nextidentval column_type;

SELECT @minidentval = MIN($IDENTITY),
    @maxidentval = MAX($IDENTITY)
FROM tablename

IF @minidentval = IDENT_SEED('tablename')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
    FROM tablename t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('tablename')
            AND @maxidentval
        AND NOT EXISTS (
            SELECT *
            FROM tablename t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('tablename')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('tablename');

SET IDENTITY_INSERT tablename OFF;

-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.
-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID('dbo.img', 'U') IS NOT NULL
    DROP TABLE img;
GO

CREATE TABLE img (
    id_num INT IDENTITY(1, 1),
    company_name SYSNAME
);

INSERT img (company_name)
VALUES ('New Moon Books');

INSERT img (company_name)
VALUES ('Lucerne Publishing');

-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON;

DECLARE @minidentval SMALLINT;
DECLARE @nextidentval SMALLINT;

SELECT @minidentval = MIN($IDENTITY)
FROM img

IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
    FROM img t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('img')
            AND 32766
        AND NOT EXISTS (
            SELECT *
            FROM img t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('img');

SET IDENTITY_INSERT img OFF;

相關內容