Menggunakan IDENTITY untuk membuat kunci pengganti menggunakan kumpulan SQL khusus di Azure Synapse Analytics

  • Artikel

Rekomendasi dan contoh untuk menggunakan properti IDENTITY untuk membuat kunci pengganti pada tabel di kumpulan SQL khusus.

Apa itu kunci pengganti

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

Catatan

Di Azure Synapse Analytics:

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

Membuat tabel dengan kolom IDENTITY

Properti IDENTITY dirancang untuk menskalakan semua distribusi di kumpulan SQL khusus tanpa mempengaruhi kinerja beban. Oleh karena itu, implementasi IDENTITY berorientasi pada pencapaian tujuan tersebut.

Anda bisa menentukan tabel sebagai properti IDENTITY 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 secara lebih lengkap.

Alokasi nilai

Properti IDENTITY tidak menjamin urutan di mana nilai pengganti dialokasikan karena arsitektur gudang data yang didistribusikan. Properti IDENTITY dirancang untuk menskalakan semua distribusi di kumpulan SQL khusus tanpa mempengaruhi kinerja 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');

Pada contoh sebelumnya, dua baris mendarat di distribusi 1. Baris pertama memiliki nilai pengganti 1 dalam kolom C1, dan baris kedua memiliki nilai pengganti 61. Kedua nilai ini dihasilkan oleh properti IDENTITY. Namun, alokasi nilai-nilai tersebut tidak berdekatan. Perilaku ini secara desain.

Data miring

Rentang nilai untuk jenis data tersebar merata di seluruh distribusi. Jika tabel terdistribusi menderita data miring, maka 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, properti IDENTITY terbatas dan hanya pada jenis data INT dan BIGINT.

SELECT..INTO

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

  • Pernyataan PILIH berisi gabungan.
  • Beberapa pernyataan PILIH digabungkan dengan menggunakan PERSATUAN.
  • Kolom IDENTITY dicantumkan lebih dari satu kali dalam daftar SELECT.
  • Kolom IDENTITY adalah bagian dari ekspresi.

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

CREATE TABLE AS SELECT

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

Menyisipkan nilai secara eksplisit ke dalam kolom IDENTITY

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

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

Skrip berikutnya memperlihatkan cara secara eksplisit menambahkan baris ini dengan menggunakan SET IDENTITAS_SISIPAN:

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 properti IDENTITY 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

Saat ini tidak dapat digunakan CREATE TABLE AS SELECT saat ini saat memuat data ke dalam tabel dengan kolom IDENTITY.

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

Tampilan sistem

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

Untuk membantu Anda lebih memahami skema database, contoh ini menunjukkan cara mengintegrasikan sys.identity_column' 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'
;

Batasan

Properti IDENTITY tidak dapat digunakan:

  • Saat jenis data kolom bukan INT atau BIGINT
  • Saat kolom juga merupakan kunci distribusi
  • Saat tabel adalah tabel eksternal

Fungsi terkait berikut ini tidak didukung dalam kumpulan SQL khusus:

Tugas umum

Bagian ini menyediakan beberapa sampel kode yang bisa Anda gunakan untuk melakukan tugas umum saat Anda bekerja dengan kolom IDENTITY.

Kolom C1 adalah IDENTITY di semua tugas berikut ini.

Menemukan nilai tertinggi yang dialokasikan untuk tabel

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

SELECT MAX(C1)
FROM dbo.T1

Menemukan seed dan kenaikan untuk properti IDENTITY

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

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

Langkah berikutnya