Fabric Data Warehouse 中的身份欄位

適用於：Microsoft Fabric 中的✅ 資料庫

在 Fabric Data Warehouse 中，IDENTITY當你在資料表中插入新列時，欄位會自動產生新的數值。

代理金鑰是在資料倉儲中使用的識別碼，可用以唯一識別資料列，並與其自然金鑰無關。 本文說明如何使用 IDENTITY 建立及管理代理金鑰，包括插入明確指定的值以及重設種子。

為什麼要使用身份欄位？

IDENTITY 欄位消除手動鍵值分配，降低錯誤風險並簡化資料擷取。 系統管理的唯一值非常適合作為代理金鑰和主金鑰。 與手動方法相比， IDENTITY 欄位的效能較佳，因為唯一鍵可自動產生，無需額外查詢邏輯。

欄位所需的 IDENTITYbigint 資料型態，最多可儲存 9,223,372,036,854,775,807 個正整數值。 此範圍可確保在資料表的整個生命週期內，每一列在其 IDENTITY 欄中都會有一個唯一值。

若有計畫將資料從其他資料庫平台遷移至替代金鑰，請參見 「將 IDENTITY 欄位遷移至 Fabric Data Warehouse」。

語法

要在Fabric Data Warehouse中定義欄位IDENTITY，請使用IDENTITY欄位定義中的屬性：

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
    [ column_name ] BIGINT IDENTITY ,
    [ ,...n ]
    -- Other columns here
);

身份欄位不一定是表格定義的第一欄。

IDENTITY 專欄的運作方式

在Fabric Data Warehouse中，你無法指定自訂起始值或增量。 系統內部管理數值以確保獨特性。 IDENTITY 欄位總是產生正整數值。 每一列新增的值都會獲得一個新值，且只要資料表存在，唯一性都會被保證。 一旦使用了一個值， IDENTITY 就不會再用同一個值。 欄位產生的數值 IDENTITY 中可能會出現空隙。

價值分配

由於倉庫引擎的分散式架構，該 IDENTITY 屬性無法保證代理值的分配順序。 此特性會跨計算節點擴展，以最大化平行性，同時不影響負載效能。 因此，不同攝取任務的價值範圍可能不是連續的。

下列範例可說明此行為：

-- Create a table with an IDENTITY column
CREATE TABLE dbo.Table1(
    Column1 BIGINT IDENTITY,
    Column2 VARCHAR(30) NULL
)

-- Ingestion task A
INSERT INTO dbo.Table1
VALUES (NULL), (NULL), (NULL), (NULL);

-- Ingestion task B
INSERT INTO dbo.Table1
VALUES (NULL), (NULL), (NULL), (NULL);

-- Review the data
SELECT * FROM dbo.Table1;

範例結果：

查詢一個有兩欄分別標記為 Column1 和 Column2 的資料表的結果集截圖，顯示八列資料。第 1 欄包含大型數值，第 2 欄為文字。

在此範例中， Ingestion task A 和 Ingestion task B 分別作為獨立任務依序執行。 雖然這些任務是連續執行的，但前四列和最後四列在 dbo.Table1.Column1 中具有不同的識別碼索引鍵範圍。 任務 A 與任務 B 之間的範圍間也可能出現空隙。

IDENTITY 在 Fabric Data Warehouse 中，可保證 IDENTITY 欄位中的所有值皆為唯一，只要未使用 IDENTITY_INSERT，但為擷取工作產生的範圍可能會出現間隔。

系統元資料物件

以下系統元資料物件在設計與處理Fabric Data Warehouse身份值時可用且非常有用。

以sys.identity_columns系統視圖列出身份欄位

使用 sys.identity_columns 目錄檢視來列出倉庫中的所有身份欄位。 以下範例列出所有包含欄位 IDENTITY 的資料表，包括結構、資料表及身份欄位名稱：

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS IdentityColumnName
FROM
    sys.identity_columns AS ic
INNER JOIN
    sys.columns AS c ON ic.[object_id] = c.[object_id]
    AND ic.column_id = c.column_id
INNER JOIN
    sys.tables AS t ON ic.[object_id] = t.[object_id]
INNER JOIN
    sys.schemas AS s ON t.[schema_id] = s.[schema_id]
ORDER BY
    s.name, t.name;

在 Fabric Data Warehouse 中，NULL 的 seed_value 與 increment_value 欄位會傳回 sys.identity_columns，且在建立識別欄位後不會更新。 last_value 資料行預設會傳回 NULL，但在資料表上第一次執行識別插入作業後，會永久切換為 -1。

以 IDENTITY_INSERT 插入數值

預設情況下，你無法將數值插入欄位 IDENTITY 。 不過，你可能需要在資料遷移、災難復原，或是填充哨兵值時（例如 -1 維度表中的「未知」）時插入特定值。

使用 SET IDENTITY_INSERT 暫時允許在識別欄位中明確指定插入值：

SET IDENTITY_INSERT dbo.DimCustomer ON;

INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-1, 'John Doe', 'john@contoso.com');

SET IDENTITY_INSERT dbo.DimCustomer OFF;

當 IDENTITY_INSERT 是 ON：

  • 語句中需 INSERT 附列列表。
  • 每個工作階段一次只能有一個資料表將 IDENTITY_INSERT 設為 ON。

這很重要

關閉 IDENTITY_INSERT 後，請 用 DBCC CHECKIDENT 重新種入身份值。

用 DBCC CHECKIDENT 重新種入身份值

插入 IDENTITY_INSERT 的明確指定值後，使用 DBCC CHECKIDENT 重新設定識別資料行的種子值。 此 RESEED 操作會掃描分散式計算節點間所有已使用及保留的身份範圍，以確定正確的下一個值，確保唯一性並防止關鍵碰撞。

DBCC CHECKIDENT('dbo.DimProduct', RESEED);

在Fabric Data Warehouse中，DBCC CHECKIDENT只支援這個RESEED選項。 資料倉儲會自動判定正確的下一個值範圍，且您無法指定自訂的重新植入值。 如需詳細資訊，請參閱 DBCC CHECKIDENT。

局限性

更多資訊請參閱 IDENTITY 欄位、IDENTITY （Transact-SQL） 以及在 Microsoft Fabric 中建立倉庫中的資料表。

  • Fabric Data Warehouse 中欄位只支援 IDENTITY 資料型態。 其他資料型態則會產生錯誤。
  • 不支援定義種子與增量。 系統內部管理價值。
  • IDENTITY不支援將欄位加入現有資料表。ALTER TABLE 考慮使用 CREATE TABLE AS SELECT（CTA） 或 SELECT...INTO 以建立現有資料表的複製品並新增 IDENTITY 欄位。
  • 當你使用 CTAS 或 IDENTITY從另一個資料表選取資料來建立資料表時，SELECT...INTO資料行的保留方式會受到限制。 欲了解更多資訊，請參閱 SELECT - INTO 條款（Transact-SQL）中的資料型別部分。
  • DBCC CHECKIDENT 只支援這個 RESEED 選項。 不支援指定自訂的重種值或使用 NORESEED 。
  • IDENTITY 欄位產生的值保證是唯一，但這些值不一定是連續或有序的，且可能出現空檔。

範例

A。 建立一個帶有 IDENTITY 欄位的資料表

CREATE TABLE Employees (
    EmployeeID BIGINT IDENTITY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

此陳述句會建立一個 Employees 表格，每新增一列自動獲得唯一 EmployeeID 值作為 bigint 值。

B. 將資料列插入具有識別欄位的資料表中

當你依定義順序為每個非單位元欄位提供值時，就不需要指定欄位清單：

INSERT INTO Employees VALUES ('Quarantino', 'Esposito');

你也可以提供一個省略身份欄位的欄位列表：

INSERT INTO Employees (FirstName, LastName)
VALUES ('Ensi', 'Vasala');

C. 以 IDENTITY_INSERT 插入明確的數值

SET IDENTITY_INSERT dbo.Employees ON;

INSERT INTO dbo.Employees (EmployeeID, FirstName, LastName)
VALUES (100, 'Sentinel', 'Row');

SET IDENTITY_INSERT dbo.Employees OFF;

D. 用 COPY INTO 插入明確值

此 COPY INTO 陳述支援 IDENTITY_INSERT 選項，以讀入在命令中明確指定的值。 COPY INTO 選項覆蓋了 對 的任何 IDENTITY_INSERT會話層級設定。

COPY INTO dbo.Employees (EmployeeID 1, FirstName 2, LastName 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    IDENTITY_INSERT = 'ON'
);

E. 在明確插入後重新種回資料表

DBCC CHECKIDENT('dbo.Employees', RESEED);

F. 使用 CREATE TABLE AS SELECT 建立資料表

使用 CTAS 建立一個資料表的副本，並將該 IDENTITY 屬性持久化在目標資料表中：

CREATE TABLE RetiredEmployees
AS SELECT * FROM Employees;

目標資料表中的欄位繼承了來源資料表的 IDENTITY 屬性。 關於限制，請參閱 SELECT - INTO 條款中的資料型別章節。

G. 使用 SELECT...INTO 建立資料表

使用 SELECT...INTO 來建立資料表的複製品，並將該 IDENTITY 屬性持久化在目標資料表中：

SELECT *
INTO dbo.RetiredEmployees
FROM dbo.Employees
WHERE LastName = 'Esposito';

目標資料表中的欄位繼承了來源資料表的 IDENTITY 屬性。 關於限制，請參閱 SELECT - INTO 條款中的資料型別章節。

後續步驟

在Fabric Data Warehouse中使用身份欄位

相關內容

  • 上次更新時間：