Kolom IDENTITY di Fabric Data Warehouse

Diterapkan pada:✅ Gudang di Microsoft Fabric

Dalam Fabric Data Warehouse, IDENTITY kolom secara otomatis menghasilkan nilai numerik baru saat Anda memasukkan baris baru ke dalam tabel.

Kunci pengganti adalah pengidentifikasi yang digunakan dalam pergudangan data untuk membedakan baris secara unik, terlepas dari kunci alaminya. Artikel ini menjelaskan cara membuat dan mengelola kunci surrogate menggunakan IDENTITY, termasuk memasukkan nilai eksplisit dan melakukan reseeding.

Mengapa menggunakan kolom IDENTITY?

IDENTITY Kolom menghilangkan penugasan kunci manual, mengurangi risiko kesalahan dan menyederhanakan pengambilan data. Nilai unik yang dikelola sistem sangat ideal sebagai kunci pengganti dan kunci primer. Dibandingkan dengan pendekatan manual, IDENTITY kolom menawarkan performa yang lebih baik karena kunci unik dihasilkan secara otomatis tanpa logika kueri tambahan.

Tipe data bigint , yang diperlukan untuk IDENTITY kolom, dapat menyimpan hingga 9.223.372.036.854.775.807 nilai bilangan bulat positif. Rentang ini memastikan setiap baris menerima nilai unik di kolomnya IDENTITY sepanjang masa pakai tabel.

Untuk rencana migrasi data dengan kunci pengganti dari platform database lain, lihat Memigrasikan kolom IDENTITY ke Fabric Data Warehouse.

Syntax

Untuk mendefinisikan kolom IDENTITY di Fabric Data Warehouse, gunakan properti dalam IDENTITY definisi kolom:

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

Kolom identitas tidak harus menjadi kolom pertama dalam definisi tabel.

Cara kerja kolom IDENTITY

Di Fabric Data Warehouse, Anda tidak dapat menentukan nilai awal atau kenaikan khusus. Sistem mengelola nilai secara internal untuk memastikan keunikan. IDENTITY kolom selalu menghasilkan nilai bilangan bulat positif. Setiap baris baru menerima nilai baru, dan keunikan dijamin selama tabel ada. Setelah nilai digunakan, IDENTITY tidak akan menggunakan nilai yang sama lagi. Celah dapat muncul pada nilai yang dihasilkan kolom IDENTITY .

Alokasi nilai

Karena arsitektur terdistribusi mesin gudang, properti IDENTITY tidak menjamin urutan alokasi nilai surrogate. Properti ini dapat diskalakan di seluruh node komputasi untuk memaksimalkan paralelisme tanpa memengaruhi performa beban. Akibatnya, rentang nilai dari tugas pengambilan yang berbeda mungkin tidak berurutan.

Contoh berikut mengilustrasikan perilaku ini:

-- 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;

Hasil sampel:

Dalam contoh ini, Ingestion task A dan Ingestion task B dijalankan secara berurutan sebagai tugas independen. Meskipun tugas-tugas berjalan secara berurutan, empat baris pertama dan empat baris terakhir memiliki rentang kunci identitas yang berbeda dalam dbo.Table1.Column1. Celah antara rentang yang ditetapkan untuk tugas A dan tugas B juga dapat terjadi.

IDENTITY di Fabric Data Warehouse menjamin bahwa semua nilai dalam kolom IDENTITY unik selama IDENTITY_INSERT tidak digunakan, tetapi jeda dapat muncul dalam rentang yang dihasilkan untuk tugas penyerapan data.

Objek metadata sistem

Objek metadata sistem berikut tersedia dan berguna saat merancang dan bekerja dengan nilai identitas di Fabric Data Warehouse.

Daftarkan kolom identitas dengan tampilan sistem sys.identity_columns

Gunakan tampilan katalog sys.identity_columns untuk mencantumkan semua kolom identitas dalam gudang. Contoh berikut mencantumkan semua tabel yang berisi kolom IDENTITY , termasuk nama skema, tabel, dan kolom identitas:

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;

Di Fabric Data Warehouse, kolom seed_value dan sys.identity_columns dari NULL mengembalikan increment_value dan tidak diperbarui setelah kolom identitas dibuat. Kolom last_value secara default mengembalikan NULL, tetapi beralih secara permanen ke -1 setelah operasi penyisipan identitas pertama pada tabel.

Sisipkan nilai dengan IDENTITY_INSERT

Secara default, Anda tidak dapat menyisipkan nilai ke dalam kolom IDENTITY . Namun, Anda mungkin perlu memasukkan nilai tertentu selama migrasi data, pemulihan bencana, atau saat mengisi nilai sentinel, seperti -1 untuk "Tidak Dikenal" dalam tabel dimensi.

Gunakan SET IDENTITY_INSERT untuk sementara mengizinkan sisipan eksplisit ke dalam kolom identitas:

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;

Saat IDENTITY_INSERT adalah ON:

Daftar kolom diperlukan dengan pernyataan INSERT tersebut.
Hanya satu tabel per sesi yang dapat memiliki IDENTITY_INSERT yang disetel ke ON pada satu waktu.

Penting

Setelah menonaktifkan IDENTITY_INSERT, setel ulang nilai identitas dengan DBCC CHECKIDENT.

Mengatur ulang nilai identitas dengan DBCC CHECKIDENT

Setelah Anda menyisipkan nilai eksplisit dengan IDENTITY_INSERT, gunakan DBCC CHECKIDENT untuk menanam ulang kolom identitas. Operasi RESEED ini memindai semua rentang ID yang digunakan dan dicadangkan di seluruh node komputasi terdistribusi untuk menentukan nilai berikutnya yang tepat, memastikan keunikan dan mencegah tabrakan kunci.

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

Di Fabric Data Warehouse, DBCC CHECKIDENT hanya mendukung opsi tersebutRESEED. Gudang data secara otomatis menetapkan rentang nilai berikutnya yang tepat, dan Anda tidak dapat menentukan nilai pengaturan ulang khusus. Untuk informasi selengkapnya, lihat DBCC CHECKIDENT.

Keterbatasan

Untuk informasi lebih lanjut, lihat kolom IDENTITY, IDENTITY (Transact-SQL), dan Buat tabel di Gudang Microsoft Fabric.

Hanya jenis data bigint yang didukung untuk IDENTITY kolom di Fabric Data Warehouse. Tipe data lain menghasilkan kesalahan.
Mendefinisikan seed dan increment tidak didukung. Sistem mengelola nilai secara internal.
Menambahkan IDENTITY kolom ke tabel yang sudah ada dengan ALTER TABLE tidak didukung. Pertimbangkan menggunakan CREATE TABLE AS SELECT (CTAS) atau SELECT... INTO untuk membuat salinan tabel yang sudah ada dan menambahkan IDENTITY kolom.
Ada batasan terkait cara kolom IDENTITY dipertahankan saat Anda membuat tabel dengan memilih data dari tabel lain menggunakan CTAS atau SELECT...INTO. Untuk informasi lebih lanjut, lihat bagian Tipe Data pada SELECT - INTO Clause (Transact-SQL).
DBCC CHECKIDENT hanya mendukung opsi tersebut RESEED . Menentukan nilai reseed khusus atau menggunakan NORESEED tidak didukung.
IDENTITY Kolom menghasilkan nilai yang dijamin unik, tetapi nilainya tidak selalu berurutan atau terurut, dan jeda dapat terjadi.

Examples

A. Membuat tabel dengan kolom IDENTITY

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

Pernyataan ini membuat Employees tabel di mana setiap baris baru secara otomatis menerima nilai unik EmployeeID sebagai bigint .

B. Sisipkan baris ke dalam tabel dengan kolom identitas

Ketika Anda menyediakan nilai untuk setiap kolom nonidentitas dalam urutan yang ditentukan, Anda tidak perlu menentukan daftar kolom:

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

Anda juga dapat menyediakan daftar kolom yang menghilangkan kolom identitas:

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

C. Sisipkan nilai eksplisit dengan 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. Masukkan nilai eksplisit dengan COPY INTO

Pernyataan COPY INTO mendukung opsi IDENTITY_INSERT untuk memasukkan nilai eksplisit di dalam perintah. COPY INTO opsi akan menggantikan pengaturan pada tingkat sesi apa pun untuk 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. Menyemai ulang tabel setelah sisipan eksplisit

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

F. Buat tabel dengan CREATE TABLE AS SELECT

Gunakan CTAS untuk membuat salinan tabel dan menyimpan properti tersebut IDENTITY di tabel target:

CREATE TABLE RetiredEmployees
AS SELECT * FROM Employees;

Kolom dalam tabel target mewarisi IDENTITY properti dari tabel sumber. Untuk pembatasan, lihat bagian Tipe Data pada SELECT - INTO Clause.

G. Membuat tabel dengan SELECT...INTO

Gunakan SELECT...INTO untuk membuat salinan tabel dan menyimpan properti tersebut IDENTITY di tabel target:

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