使用 IDENTITY 在專用 SQL 集區中建立代理密鑰
在本文中，您會找到使用 IDENTITY 屬性在專用 SQL 集區中的數據表上建立 Surrogate 索引鍵的建議和範例。
什麼是代理金鑰？
數據表上的 Surrogate 索引鍵是具有每個數據列唯一標識碼的數據行。 索引鍵不會從數據表數據產生。 數據模型設計師喜歡在設計數據倉儲模型時在其數據表上建立代理索引鍵。 您可以使用 IDENTITY 屬性，以簡單且有效地達成此目標，而不會影響負載效能。
注意
在 Azure Synapse Analytics 中：
- IDENTITY 值每次發佈都會自行增加，不會與其他發佈的 IDENTITY 值重疊。 如果用戶明確地使用 或 reseeds IDENTITY 插入重複的值，Synapse 中的 IDENTITY 值
SET IDENTITY_INSERT ON不保證是唯一的。 如需詳細資訊，請參閱 CREATE TABLE （Transact-SQL） IDENTITY （Property）。 - 散發數據行上的 UPDATE 不保證 IDENTITY 值是唯一的。 在散發數據行上的 UPDATE 之後，使用 DBCC CHECKIDENT （Transact-SQL） 來驗證唯一性。
建立具有 IDENTITY 資料行的數據表
屬性 IDENTITY 的設計目的是要相應放大專用 SQL 集區中的所有散發，而不會影響負載效能。 因此，的實作 IDENTITY 面向實現這些目標。
當您第一次使用類似下列語句的語法來建立資料表時，您可以將數據表定義為具有 IDENTITY 屬性：
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1) NOT NULL,
C2 INT NULL
)
WITH
( DISTRIBUTION = HASH(C2),
CLUSTERED COLUMNSTORE INDEX
);
然後 INSERT..SELECT ，您可以使用 來填入數據表。
本節的其餘部分會強調實作的細微差別，以協助您更充分了解這些實作。
值的配置
屬性 IDENTITY 不保證因為數據倉儲的分散式架構而配置代理值的順序。 屬性 IDENTITY 的設計目的是要相應放大專用 SQL 集區中的所有散發，而不會影響負載效能。
下列範例是圖例：
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1) NOT NULL,
C2 VARCHAR(30) NULL
)
WITH
( DISTRIBUTION = HASH(C2),
CLUSTERED COLUMNSTORE INDEX
);
INSERT INTO dbo.T1
VALUES (NULL);
INSERT INTO dbo.T1
VALUES (NULL);
SELECT *
FROM dbo.T1;
DBCC PDW_SHOWSPACEUSED('dbo.T1');
在上述範例中，兩個數據列落在散發 1 中。 第一個數據列在數據行中 C1具有 1 的 Surrogate 值，而第二個數據列的 Surrogate 值為 61。 這兩個值都是由 IDENTITY 屬性所產生。 不過，值的配置不是連續的。 這是依照設計的行為。
扭曲的數據
資料類型的值範圍會平均分散於發佈上。 如果分散式資料表受到偏斜資料的影響，則可供資料類型使用的值範圍可能會提前耗盡。 例如，如果所有數據最終都是在單一散發中，則數據表實際上只能存取數據類型的1-600個值。 基於這個理由， IDENTITY 屬性僅限於 INT 和 BIGINT 數據類型。
選擇。。到
當現有的 IDENTITY 數據行選取到新的數據表時，除非下列其中一個條件成立，否則新數據行會 IDENTITY 繼承 屬性：
-
SELECT語句包含聯結。 - 使用
UNION聯結多個SELECT語句。 - 數據
IDENTITY行會在清單中列出一次SELECT以上。 - 數據
IDENTITY行是表達式的一部分。
如果上述任一條件成立，則會建立 NOT NULL 數據行，而不是繼承 IDENTITY 屬性。
CREATE TABLE AS SELECT
CREATE TABLE AS SELECT （CTAS） 遵循針對 所記載 SELECT..INTO的相同 SQL Server 行為。 不過，您無法在語句部分的數據CREATE TABLE行定義中指定 IDENTITY 屬性。 您也無法在 SELECT CTAS 的部分中使用 函IDENTITY式。 若要填入資料表，您必須使用 CREATE TABLE 來定義資料表， INSERT..SELECT 後面接著填入數據表。
將明確值插入 IDENTITY 資料行
專用 SQL 集區支援 SET IDENTITY_INSERT <your table> ON|OFF 語法。 您可以使用這個語法，明確地將值插入數據 IDENTITY 行中。
許多數據模型工具，例如在其維度中使用特定數據列的預先定義負值。 例如 -1 或 未知的成員 數據列。
下一個文稿示範如何使用 明確新增此資料列 SET IDENTITY_INSERT：
SET IDENTITY_INSERT dbo.T1 ON;
INSERT INTO dbo.T1
( C1,
C2
)
VALUES (-1,'UNKNOWN');
SET IDENTITY_INSERT dbo.T1 OFF;
SELECT *
FROM dbo.T1;
載入資料
屬性的存在 IDENTITY 對數據載入程式代碼有一些影響。 本節強調使用 IDENTITY將數據載入數據表的一些基本模式。
若要將數據載入數據表，並使用 產生 Surrogate 索引鍵 IDENTITY，請建立數據表，然後使用 INSERT..SELECT 或 INSERT..VALUES 來執行載入。
下列範例會反白顯示基本模式：
--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1),
C2 VARCHAR(30)
)
WITH
( DISTRIBUTION = HASH(C2),
CLUSTERED COLUMNSTORE INDEX
);
--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT C2
FROM ext.T1;
SELECT *
FROM dbo.T1;
DBCC PDW_SHOWSPACEUSED('dbo.T1');
注意
將數據載入具有IDENTITY數據行的數據表時，目前無法使用CREATE TABLE AS SELECT。
如需載入數據的詳細資訊，請參閱 針對專用 SQL 集 區設計擷取、載入和轉換 （ELT） 和 載入最佳做法。
系統檢視表
您可以使用 sys.identity_columns 目錄檢視來識別具有 屬性的數據 IDENTITY 行。
為了協助您進一步瞭解資料庫架構，此範例示範如何與其他系統目錄檢視整合 sys.identity_columns ：
SELECT sm.name
, tb.name
, co.name
, CASE WHEN ic.column_id IS NOT NULL
THEN 1
ELSE 0
END AS is_identity
FROM sys.schemas AS sm
JOIN sys.tables AS tb ON sm.schema_id = tb.schema_id
JOIN sys.columns AS co ON tb.object_id = co.object_id
LEFT JOIN sys.identity_columns AS ic ON co.object_id = ic.object_id
AND co.column_id = ic.column_id
WHERE sm.name = 'dbo'
AND tb.name = 'T1'
;
限制
IDENTITY無法使用 屬性：
- 當數據行數據類型不是
INT或BIGINT - 當數據行也是散發索引鍵時
- 當數據表是外部數據表時
專用 SQL 集區不支援下列相關函式：
常見工作
您可以使用下列範例程式代碼，在處理 IDENTITY 數據行時執行一般工作。
資料列 C1 是 IDENTITY 下列所有工作中的 。
尋找數據表的最高配置值
使用 函 MAX() 式來判斷為分散式數據表設定的最高值：
SELECT MAX(C1)
FROM dbo.T1
尋找 IDENTITY 屬性的種子和增量
您可以使用目錄檢視來探索資料表的身分識別遞增和植入組態值，方法是使用下列查詢：
SELECT sm.name
, tb.name
, co.name
, ic.seed_value
, ic.increment_value
FROM sys.schemas AS sm
JOIN sys.tables AS tb ON sm.schema_id = tb.schema_id
JOIN sys.columns AS co ON tb.object_id = co.object_id
JOIN sys.identity_columns AS ic ON co.object_id = ic.object_id
AND co.column_id = ic.column_id
WHERE sm.name = 'dbo'
AND tb.name = 'T1'
;