CREATE TABLE (Transact-SQL) IDENTITY (Properti)

Artikel
06/24/2022
4 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance Azure 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 identitas SQL-DMO yang mengekspos properti identitas baris kolom.

Topic link icon Konvensi Sintaksis T-SQL

Sintaks

IDENTITY [ (seed , increment) ]

Catatan

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

Catatan

Untuk melihat sintaks transact-SQL untuk SQL Server 2014 dan yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

Nilai awal
Adalah nilai yang digunakan untuk baris pertama yang dimuat ke dalam tabel.

Kenaikan
Adalah nilai inkremental yang ditambahkan ke nilai identitas baris sebelumnya yang dimuat.

Catatan

Dalam nilai analitik Azure Synapse untuk identitas tidak bertahas karena arsitektur gudang data yang didistribusikan. Silakan lihat Menggunakan IDENTITY untuk membuat kunci pengganti di kumpulan SQL Synapse untuk informasi selengkapnya.

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

Keterangan

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

Setiap nilai baru dihasilkan berdasarkan kenaikan nilai awal & saat ini.
Setiap nilai baru untuk transaksi tertentu berbeda dari transaksi bersamaan lainnya pada tabel.

Properti identitas pada kolom tidak menjamin hal berikut:

Keunikan nilai - Keunikan harus diberlakukan dengan menggunakan KUNCI PRIMER atau batasan UNIK atau indeks UNIK .

Catatan

Azure Synapse Analytics tidak mendukung KUNCI PRIMER atau batasan UNIK atau indeks UNIK. Silakan lihat Menggunakan IDENTITY untuk membuat kunci pengganti di kumpulan SQL Synapse untuk informasi selengkapnya.

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 tingkat isolasi SERIALIZABLE .
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 celah dalam nilai identitas saat dimasukkan. Jika celah tidak dapat diterima maka aplikasi harus menggunakan mekanismenya sendiri untuk menghasilkan nilai kunci. Menggunakan generator urutan dengan opsi NOCACHE dapat membatasi celah untuk transaksi yang tidak pernah dilakukan.
Penggunaan kembali nilai - Untuk properti identitas tertentu dengan nilai awal/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 akan dihasilkan lagi. Ini dapat mengakibatkan celah 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 penetapan tabel dan angka 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, nilai awal dan kenaikan harus diatur ke 1,1. Mengatur nilai awal atau kenaikan ke nilai selain 1 menghasilkan kesalahan berikut: Penggunaan nilai benih dan kenaikan selain 1 tidak didukung dengan tabel memori yang dioptimalkan.

Contoh

A. Menggunakan properti IDENTITY dengan CREATE TABLE

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

USE AdventureWorks2012;  
  
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. Menggunakan 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;

Lihat juga

ALTER TABLE (Transact-SQL)
BUAT TABEL (Transact-SQL)
DBCC CHECKIDENT (SQL bertransaksi)
IDENT_INCR (SQL Bertransaksi)
@@IDENTITY (SQL bertransaksi)
IDENTITY (Function) (Transact-SQL)
IDENT_SEED (SQL Bertransaksi)
SELECT (Transact-SQL)
SET IDENTITY_INSERT (SQL T-tranact)
Mereplikasi Kolom Identitas