Menggunakan IDENTITY untuk membuat kunci pengganti di kumpulan SQL khusus

Tip

Microsoft Fabric Data Warehouse adalah gudang relasional skala perusahaan pada fondasi data lake, dengan arsitektur siap masa depan, AI bawaan, dan fitur baru. Jika Anda baru menggunakan pergudangan data, mulailah dengan Fabric Data Warehouse. Beban kerja kumpulan SQL terdedikasi yang ada dapat ditingkatkan ke Fabric untuk mengakses kemampuan baru di seluruh ilmu data, analitik waktu nyata, dan pelaporan.

• Mulai uji coba gratis Fabric.
• Migration Assistant untuk Fabric Data Warehouse.

Dalam artikel ini, Anda menemukan rekomendasi dan contoh untuk menggunakan IDENTITY properti untuk membuat kunci pengganti pada tabel di kumpulan SQL khusus.

Apa itu kunci pengganti?

Kunci pengganti pada tabel adalah kolom dengan pengidentifikasi unik untuk setiap baris. Kunci tidak dihasilkan dari data tabel. Pemodel data suka membuat kunci pengganti pada tabel mereka saat merancang model gudang data. Anda dapat menggunakan IDENTITY properti untuk mencapai tujuan ini secara sederhana dan efektif tanpa memengaruhi performa beban.

Catatan

Dalam Azure Synapse Analytics:

• Nilai IDENTITY meningkat sendiri di setiap distribusi dan tidak tumpang tindih dengan nilai IDENTITY dalam distribusi lain. Nilai IDENTITY di Synapse tidak dijamin unik jika pengguna secara eksplisit menyisipkan nilai duplikat dengan SET IDENTITY_INSERT ON atau mengubah identitas. Untuk detailnya, lihat CREATE TABLE (Transact-SQL) IDENTITY (Properti).
• Pembaruan pada kolom distribusi tidak menjamin nilai IDENTITY unik. Gunakan DBCC CHECKIDENT (Transact-SQL) setelah UPDATE pada kolom distribusi untuk memverifikasi keunikan.

Membuat tabel dengan kolom IDENTITY

IDENTITY Properti ini dirancang untuk menskalakan di semua distribusi di kumpulan SQL khusus tanpa memengaruhi performa beban. Oleh karena itu, implementasi IDENTITY berorientasi pada mencapai tujuan ini.

Anda dapat menentukan tabel sebagai memiliki IDENTITY properti saat pertama kali membuat tabel dengan menggunakan sintaks yang mirip dengan pernyataan berikut:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

Anda kemudian dapat menggunakan INSERT..SELECT untuk mengisi tabel.

Sisa bagian ini menyoroti nuansa implementasi untuk membantu Anda memahaminya lebih lengkap.

Alokasi nilai

Properti IDENTITY tidak menjamin urutan nilai pengganti dialokasikan karena arsitektur terdistribusi gudang data. IDENTITY Properti ini dirancang untuk menskalakan di semua distribusi di kumpulan SQL khusus tanpa memengaruhi performa beban.

Contoh berikut adalah ilustrasi:

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

Dalam contoh sebelumnya, dua baris ditempatkan dalam distribusi 1. Baris pertama memiliki nilai pengganti 1 dalam kolom C1, dan baris kedua memiliki nilai pengganti 61. Kedua nilai ini dihasilkan oleh IDENTITY properti . Namun, alokasi nilai tidak berurutan. Perilaku ini dirancang.

Data terdistorsi

Rentang nilai untuk jenis data tersebar merata di seluruh distribusi. Jika tabel terdistribusi menderita data miring, rentang nilai yang tersedia untuk jenis data dapat habis sebelum waktunya. Misalnya, jika semua data berakhir dalam satu distribusi, maka secara efektif tabel hanya memiliki akses ke satu-enam puluh nilai jenis data. Untuk alasan ini, IDENTITY properti hanya terbatas pada INT dan BIGINT jenis data.

SELECT..INTO

Saat kolom yang sudah ada IDENTITY dipilih ke dalam tabel baru, kolom baru mewarisi IDENTITY properti , kecuali salah satu kondisi berikut ini benar:

• SELECT berisi pernyataan yang mengandung gabungan.
• Beberapa SELECT pernyataan digabungkan dengan menggunakan UNION.
• Kolom IDENTITY dicantumkan lebih dari satu kali dalam SELECT daftar.
• Kolom IDENTITY adalah bagian dari ekspresi.

Jika salah satu kondisi ini benar, kolom dibuat NOT NULL alih-alih mewarisi IDENTITY properti.

BUAT TABEL DENGAN SELECT

CREATE TABLE AS SELECT (CTAS) mengikuti perilaku SQL Server yang sama yang didokumenkan untuk SELECT..INTO. Namun, Anda tidak dapat menentukan IDENTITY properti dalam definisi kolom bagian CREATE TABLE pernyataan. Anda juga tidak dapat menggunakan fungsi IDENTITY di bagian SELECT CTAS. Untuk mengisi tabel, Anda perlu menggunakan CREATE TABLE untuk menentukan tabel diikuti dengan INSERT..SELECT mengisinya.

Menyisipkan nilai eksplisit ke dalam kolom IDENTITY

Kumpulan SQL khusus mendukung SET IDENTITY_INSERT <your table> ON|OFF sintaksis. Anda dapat menggunakan sintaks ini untuk menyisipkan nilai secara eksplisit ke IDENTITY dalam kolom.

Banyak pemodel data suka menggunakan nilai negatif yang telah ditentukan sebelumnya untuk baris tertentu dalam dimensinya. Contohnya adalah baris anggota yang -1 atau tidak diketahui .

Skrip berikutnya menunjukkan cara menambahkan baris ini secara eksplisit dengan menggunakan 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;

Memuat data

Kehadiran IDENTITY properti memiliki beberapa implikasi pada kode pemuatan data Anda. Bagian ini menyoroti beberapa pola dasar untuk memuat data ke dalam tabel dengan menggunakan IDENTITY.

Untuk memuat data ke dalam tabel dan menghasilkan kunci pengganti dengan menggunakan IDENTITY, buat tabel lalu gunakan INSERT..SELECT atau INSERT..VALUES untuk melakukan pemuatan.

Contoh berikut menyoroti pola dasar:

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

Catatan

Tidak dimungkinkan untuk digunakan CREATE TABLE AS SELECT saat ini saat memuat data ke dalam tabel dengan IDENTITY kolom.

Untuk informasi selengkapnya tentang memuat data, lihat Merancang Ekstrak, Muat, dan Transformasi (ELT) untuk kumpulan SQL khusus dan Praktik terbaik pemuatan.

Tampilan sistem

Anda dapat menggunakan tampilan katalog sys.identity_columns untuk mengidentifikasi kolom yang memiliki IDENTITY properti .

Untuk membantu Anda lebih memahami skema database, contoh ini menunjukkan cara berintegrasi sys.identity_columns dengan tampilan katalog sistem lainnya:

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

Limitations

Properti IDENTITY tidak dapat digunakan:

• Saat tipe data kolom tidak INT atau BIGINT
• Saat kolom juga merupakan kunci distribusi
• Ketika tabel adalah tabel eksternal

Fungsi terkait berikut ini tidak didukung di kumpulan SQL khusus:

• IDENTITY()
• @@IDENTITY
• SCOPE_IDENTITY
• IDENT_CURRENT
• IDENT_INCR
• IDENT_SEED

Tugas umum

Anda dapat menggunakan kode sampel berikut untuk melakukan tugas umum saat Anda bekerja dengan IDENTITY kolom.

Kolom C1 adalah IDENTITY dalam semua tugas berikut.

Menemukan nilai tertinggi yang dialokasikan untuk tabel

MAX() Gunakan fungsi untuk menentukan nilai tertinggi yang dialokasikan untuk tabel terdistribusi:

SELECT MAX(C1)
FROM dbo.T1

Temukan nilai awal dan kenaikan untuk properti IDENTITY

Anda bisa menggunakan tampilan katalog untuk menemukan kenaikan identitas dan nilai konfigurasi benih untuk tabel dengan menggunakan kueri berikut:

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

Konten terkait

• Mendesain tabel menggunakan kumpulan SQL khusus
• CREATE TABLE (Transact-SQL) IDENTITY (Properti)
• DBCC CHECKIDENT (Transact-SQL)