Menghasilkan nilai otomatis

3 menit

Anda mungkin perlu secara otomatis menghasilkan nilai berurutan untuk satu kolom dalam tabel tertentu. Transact-SQL menyediakan dua cara untuk melakukan ini: gunakan properti IDENTITY dengan kolom tertentu dalam tabel, atau tentukan objek SEQUENCE dan gunakan nilai yang dihasilkan oleh objek tersebut.

Properti IDENTITY

Untuk menggunakan properti IDENTITY, tentukan kolom menggunakan tipe data numerik dengan skala 0 (berarti hanya bilangan bulat) dan sertakan kata kunci IDENTITY. Jenis yang diizinkan mencakup semua jenis integer dan jenis desimal di mana Anda secara eksplisit memberikan skala 0.

Seed opsional (nilai awal), dan tahapan (nilai langkah) juga dapat ditentukan. Meninggalkan seed dan tahapan akan membuat keduanya menjadi 1.

Catatan

Properti IDENTITY ditentukan di tempat menentukan NULL atau NOT NULL dalam definisi kolom. Setiap kolom dengan properti IDENTITY secara otomatis tidak dapat diubah ke null. Anda dapat menentukan NOT NULL hanya untuk dokumentasi diri, tetapi jika Anda menetapkan kolom sebagai NULL (artinya dapat diubah ke null), pernyataan pembuatan tabel akan menghasilkan kesalahan.

Hanya satu kolom dalam tabel yang mungkin memiliki kumpulan properti IDENTITY; ini sering digunakan sebagai PRIMARY KEY atau kunci alternatif.

Kode berikut menunjukkan pembuatan tabel Sales.Promotion yang digunakan dalam contoh bagian sebelumnya, namun kali ini dengan kolom identitas bernama PromotionID sebagai kunci utama:

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Catatan

Detail lengkap dari pernyataan CREATE TABLE berada di luar cakupan modul ini.

Menyisipkan data ke dalam kolom identitas

Saat properti IDENTITY ditentukan untuk sebuah kolom, pernyataan INSERT ke dalam tabel umumnya tidak menentukan nilai untuk kolom IDENTITY. Mesin database menghasilkan nilai menggunakan nilai yang tersedia berikutnya untuk kolom.

Misalnya, Anda dapat menyisipkan baris ke dalam tabel Sales.Promotion tanpa menentukan nilai untuk kolom PromotionID:

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Perhatikan bahwa meskipun klausul VALUES tidak menyertakan nilai untuk kolom PromotionID, Anda tidak perlu menentukan daftar kolom dalam klausul INSERT - Kolom identitas dikecualikan dari persyaratan ini.

Jika baris ini adalah yang pertama disisipkan ke dalam tabel, hasilnya adalah baris baru seperti ini:

PromotionID

PromotionName

StartDate

ProductModelID

Diskon

Catatan

Obral

2021-01-01T00:00:00

0.1

Diskon 10%

Saat tabel dibuat, tidak ada nilai seed atau tahapan yang ditetapkan untuk kolom IDENTITY, jadi baris pertama disisipkan dengan nilai 1. Baris berikutnya yang akan disisipkan akan diberi nilai PromotionID 2, dan seterusnya.

Mengambil nilai identitas

Untuk mengembalikan nilai IDENTITY yang baru saja ditetapkan dalam sesi dan cakupan yang sama, gunakan fungsi SCOPE_IDENTITY; seperti ini:

SELECT SCOPE_IDENTITY();

Fungsi SCOPE_IDENTITY mengembalikan nilai identitas terbaru yang dihasilkan dalam cakupan saat ini untuk tabel apa pun. Jika Anda memerlukan nilai identitas terbaru dalam tabel tertentu, Anda dapat menggunakan fungsi IDENT_CURRENT, seperti ini:

SELECT IDENT_CURRENT('Sales.Promotion');

Mengambil alih nilai identitas

Jika Anda ingin mengganti nilai yang dihasilkan secara otomatis dan menetapkan nilai tertentu ke kolom IDENTITY, Anda harus terlebih dahulu mengaktifkan penyisipan identitas menggunakan pernyataan SET IDENTITY INSERT table_name ON. Dengan opsi ini diaktifkan, Anda dapat memasukkan nilai eksplisit untuk kolom identitas, sama seperti kolom lainnya. Setelah selesai, Anda dapat menggunakan pernyataan SET IDENTITY INSERT table_name OFF untuk melanjutkan menggunakan nilai identitas otomatis, menggunakan nilai terakhir yang Anda masukkan secara eksplisit sebagai seed.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

Seperti yang telah Anda pelajari, properti IDENTITY digunakan untuk menghasilkan urutan nilai untuk kolom dalam tabel. Namun, properti IDENTITY tidak cocok untuk mengoordinasikan nilai di beberapa tabel dalam database. Misalnya, organisasi Anda membedakan antara penjualan langsung dan penjualan ke penjual, dan ingin menyimpan data untuk penjualan ini dalam tabel terpisah. Kedua jenis penjualan mungkin memerlukan nomor faktur yang unik, dan Anda mungkin ingin menghindari duplikasi nilai yang sama untuk dua jenis penjualan yang berbeda. Salah satu solusi untuk persyaratan ini adalah mempertahankan kumpulan nilai berurutan unik di kedua tabel.

Reseeding kolom identitas

Terkadang, Anda harus mengatur ulang atau melewati nilai identitas untuk kolom tersebut. Untuk melakukan ini, Anda akan "melakukan reseeding" kolom menggunakan fungsi DBCC CHECKIDENT. Anda dapat menggunakan ini untuk melewati banyak nilai, atau untuk mereset nilai identitas berikutnya ke 1 setelah Anda menghapus semua baris dalam tabel. Untuk detail selengkapnya menggunakan DBCC CHECKIDENT, lihat dokumentasi referensi Transact-SQL.

SEQUENCE

Di Transact-SQL, Anda dapat menggunakan objek urutan untuk menentukan nilai sekuensial baru secara independen dari tabel tertentu. Objek urutan dibuat menggunakan pernyataan CREATE SEQUENCE, secara opsional menyediakan tipe data (harus berupa jenis integer atau desimal atau numerik dengan skala 0), nilai awal, nilai tahapan, nilai maksimum, dan opsi lain yang terkait dengan performa.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

Untuk mengambil nilai berikutnya yang tersedia dari suatu urutan, gunakan konstruksi NEXT VALUE FOR, seperti ini:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY atau SEQUENCE

Saat memutuskan apakah akan menggunakan kolom IDENTITY atau objek SEQUENCE untuk mengisi nilai secara otomatis, ingatlah poin-poin berikut:

Gunakan SEQUENCE jika aplikasi Anda memerlukan berbagi satu seri angka antara beberapa tabel atau beberapa kolom dalam sebuah tabel.
SEQUENCE memungkinkan Anda untuk mengurutkan nilai menurut kolom lain. Konstruksi NEXT VALUE FOR dapat menggunakan klausul OVER untuk menentukan kolom urut. Klausul OVER menjamin bahwa nilai yang dikembalikan dihasilkan dalam urutan klausul ORDER BY klausul OVER. Fungsionalitas ini juga memungkinkan Anda menghasilkan nomor baris untuk baris saat dikembalikan dalam SELECT. Pada contoh berikut, tabel Production.Product diurutkan menurut kolom Nama dan kolom yang dikembalikan pertama adalah nomor berurutan.
```
SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;
```
Meskipun pernyataan sebelumnya hanya memilih nilai SEQUENCE untuk ditampilkan, nilainya masih 'habis' dan nilai SEQUENCE yang ditampilkan tidak akan tersedia lagi. Jika Anda menjalankan SELECT di atas beberapa kali, Anda akan mendapatkan nilai SEQUENCE yang berbeda setiap kali.
Gunakan SEQUENCE jika aplikasi Anda memerlukan beberapa nomor untuk ditetapkan secara bersamaan. Misalnya, aplikasi perlu memesan lima nomor berurutan. Meminta nilai identitas dapat mengakibatkan kesenjangan dalam rangkaian jika proses lain secara bersamaan mengeluarkan nomor. Anda dapat menggunakan prosedur sistem sp_sequence_get_range untuk mengambil beberapa nomor dalam urutan sekaligus.
SEQUENCE memungkinkan Anda untuk mengubah spesifikasi urutan, seperti nilai tahapan.
Nilai IDENTITY dilindungi dari pembaruan. Jika Anda mencoba memperbarui kolom dengan properti IDENTITY, Anda akan mendapatkan kesalahan.

Lanjutkan

Menghasilkan nilai otomatis

Properti IDENTITY

Menyisipkan data ke dalam kolom identitas

Mengambil nilai identitas

Mengambil alih nilai identitas

Reseeding kolom identitas

SEQUENCE

IDENTITY atau SEQUENCE

Saran dan Komentar