CREATE TABLE (Transact-SQL) IDENTITY (屬性)
適用於：
sql ServerAzure SQL 資料庫 Azure SQL 受控執行個體 Microsoft Fabric 中的 Azure Synapse Analytics SQL 資料庫
建立資料表中的識別欄位。 這個屬性會搭配 CREATE TABLE 和 ALTER TABLE Transact-SQL 陳述式使用。
注意
IDENTITY 屬性與公開數據行之數據列識別屬性的 SQL-DMO Identity 屬性不同。
Syntax
IDENTITY [ (seed , increment) ]
Azure Synapse Analytics 的無伺服器 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;