CREATE TABLE (Transact-SQL) IDENTITY (Properti)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Membuat kolom identitas dalam tabel. Properti ini digunakan dengan pernyataan CREATE TABLE dan ALTER TABLE Transact-SQL.

Catatan

Properti IDENTITY berbeda dari properti SQL-DMO Identity yang mengekspos properti identitas baris kolom.

Konvensi sintaks transact-SQL

Sintaks

IDENTITY [ (seed , increment) ]

Sintaks ini tidak didukung oleh kumpulan SQL tanpa server di Azure Synapse Analytics.

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

Benih

Nilai yang digunakan untuk baris pertama yang dimuat ke dalam tabel.

Kenaikan

Nilai inkremental yang ditambahkan ke nilai identitas baris sebelumnya yang dimuat.

Catatan

Dalam nilai Azure Synapse Analytics untuk identitas tidak bertahap karena arsitektur terdistribusi gudang data. Untuk informasi selengkapnya, lihat Menggunakan IDENTITY untuk membuat kunci pengganti di kumpulan Synapse SQL.

Anda harus menentukan benih dan kenaikan atau tidak. Jika tidak ditentukan, defaultnya adalah (1,1).

Keterangan

Kolom identitas dapat digunakan untuk menghasilkan nilai kunci. Properti identitas pada kolom menjamin kondisi berikut:

  • Setiap nilai baru dihasilkan berdasarkan benih dan kenaikan saat ini.

  • Setiap nilai baru untuk transaksi tertentu berbeda dari transaksi bersamaan lainnya pada tabel.

Properti identitas pada kolom tidak menjamin kondisi berikut:

  • Keunikan nilai - Keunikan harus diberlakukan dengan menggunakan PRIMARY KEY batasan atau indeks atau UNIQUEUNIQUE .

    Catatan

    Azure Synapse Analytics tidak mendukung PRIMARY KEY atau membatasi atau UNIQUEUNIQUE mengindeks. Untuk informasi selengkapnya, lihat Menggunakan IDENTITY untuk membuat kunci pengganti di kumpulan Synapse SQL.

  • Nilai berturut-turut dalam transaksi - Transaksi yang menyisipkan beberapa baris tidak dijamin untuk mendapatkan nilai berturut-turut untuk baris karena sisipan bersamaan lainnya mungkin terjadi pada tabel. Jika nilai harus berturut-turut, maka transaksi harus menggunakan kunci eksklusif pada tabel atau menggunakan SERIALIZABLE tingkat isolasi.

  • Nilai berturut-turut setelah menghidupkan ulang server atau kegagalan lainnya - SQL Server mungkin menyimpan nilai identitas karena alasan performa dan beberapa nilai yang ditetapkan dapat hilang selama kegagalan database atau menghidupkan ulang server. Ini dapat mengakibatkan kesenjangan dalam nilai identitas saat disisipkan. Jika celah tidak dapat diterima, aplikasi harus menggunakan mekanismenya sendiri untuk menghasilkan nilai kunci. Menggunakan generator urutan dengan NOCACHE opsi dapat membatasi celah untuk transaksi yang tidak pernah dilakukan.

  • Penggunaan kembali nilai - Untuk properti identitas tertentu dengan benih/kenaikan tertentu, nilai identitas tidak digunakan kembali oleh mesin. Jika pernyataan sisipan tertentu gagal, atau jika pernyataan sisipan digulung balik, maka nilai identitas yang digunakan hilang dan tidak dihasilkan lagi. Ini dapat mengakibatkan kesenjangan ketika nilai identitas berikutnya dihasilkan.

Pembatasan ini adalah bagian dari desain untuk meningkatkan performa, dan karena dapat diterima dalam banyak situasi umum. Jika Anda tidak dapat menggunakan nilai identitas karena pembatasan ini, buat tabel terpisah yang menyimpan nilai saat ini dan kelola akses ke tabel dan penetapan nomor dengan aplikasi Anda.

Jika tabel dengan kolom identitas diterbitkan untuk replikasi, kolom identitas harus dikelola dengan cara yang sesuai untuk jenis replikasi yang digunakan. Untuk informasi selengkapnya, lihat Mereplikasi Kolom Identitas.

Hanya satu kolom identitas yang dapat dibuat per tabel.

Dalam tabel memori yang dioptimalkan, benih dan kenaikan harus diatur ke 1, 1. Mengatur benih atau kenaikan ke nilai selain 1 menghasilkan kesalahan berikut: The use of seed and increment values other than 1 is not supported with memory optimized tables.

Setelah properti identitas diatur pada kolom, properti tidak dapat dihapus. Jenis data dapat diubah selama jenis data baru kompatibel dengan properti identitas.

Contoh

J. Menggunakan properti IDENTITY dengan CREATE TABLE

Contoh berikut membuat tabel baru menggunakan IDENTITY properti untuk nomor identifikasi tambahan secara otomatis.

USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.new_employees', 'U') IS NOT NULL
    DROP TABLE new_employees;
GO

CREATE TABLE new_employees (
    id_num INT IDENTITY(1, 1),
    fname VARCHAR(20),
    minit CHAR(1),
    lname VARCHAR(30)
);

INSERT new_employees (fname, minit, lname)
VALUES ('Karin', 'F', 'Josephs');

INSERT new_employees (fname, minit, lname)
VALUES ('Pirkko', 'O', 'Koskitalo');

B. Gunakan sintaks generik untuk menemukan celah dalam nilai identitas

Contoh berikut menunjukkan sintaksis generik untuk menemukan celah dalam nilai identitas saat data dihapus.

Catatan

Bagian pertama dari skrip Transact-SQL berikut dirancang hanya untuk ilustrasi. Anda dapat menjalankan skrip Transact-SQL yang dimulai dengan komentar: -- Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON;

DECLARE @minidentval column_type;
DECLARE @maxidentval column_type;
DECLARE @nextidentval column_type;

SELECT @minidentval = MIN($IDENTITY),
    @maxidentval = MAX($IDENTITY)
FROM tablename

IF @minidentval = IDENT_SEED('tablename')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
    FROM tablename t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('tablename')
            AND @maxidentval
        AND NOT EXISTS (
            SELECT *
            FROM tablename t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('tablename')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('tablename');

SET IDENTITY_INSERT tablename OFF;

-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.
-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID('dbo.img', 'U') IS NOT NULL
    DROP TABLE img;
GO

CREATE TABLE img (
    id_num INT IDENTITY(1, 1),
    company_name SYSNAME
);

INSERT img (company_name)
VALUES ('New Moon Books');

INSERT img (company_name)
VALUES ('Lucerne Publishing');

-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON;

DECLARE @minidentval SMALLINT;
DECLARE @nextidentval SMALLINT;

SELECT @minidentval = MIN($IDENTITY)
FROM img

IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
    FROM img t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('img')
            AND 32766
        AND NOT EXISTS (
            SELECT *
            FROM img t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('img');

SET IDENTITY_INSERT img OFF;

Konten terkait