Nomor Urut

Berlaku untuk: SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Artikel ini menjelaskan cara menggunakan nomor urut di SQL Server, Azure SQL Database, dan Azure SQL Managed Instance. Urutannya adalah objek terikat skema yang ditentukan pengguna yang menghasilkan urutan nilai numerik sesuai dengan spesifikasi yang urutannya dibuat.

Gambaran Umum

Urutan nilai numerik dihasilkan dalam urutan naik atau turun pada interval yang ditentukan dan mungkin siklus (ulangi) seperti yang diminta. Urutan, tidak seperti kolom identitas, tidak terkait dengan tabel. Aplikasi mengacu pada objek urutan untuk menerima nilai berikutnya. Hubungan antara urutan dan tabel dikontrol oleh aplikasi. Aplikasi pengguna dapat mereferensikan objek urutan dan mengoordinasikan kunci nilai di beberapa baris dan tabel.

Urutan dibuat secara independen dari tabel dengan menggunakan pernyataan CREATE SEQUENCE . Opsi memungkinkan Anda mengontrol kenaikan, nilai maksimum dan minimum, titik awal, kemampuan untuk memulai ulang secara otomatis, dan cache untuk meningkatkan performa. Untuk informasi tentang opsi, lihat MEMBUAT URUTAN.

Tidak seperti nilai kolom identitas, yang dihasilkan saat baris disisipkan, aplikasi dapat memperoleh nomor urutan berikutnya sebelum menyisipkan baris dengan memanggil fungsi NEXT VALUE FOR . Nomor urut dialokasikan ketika NEXT VALUE FOR dipanggil meskipun angka tidak pernah disisipkan ke dalam tabel. Fungsi NEXT VALUE FOR dapat digunakan sebagai nilai default untuk kolom dalam definisi tabel. Gunakan sp_sequence_get_range untuk mendapatkan rentang beberapa nomor urut sekaligus.

Urutan dapat didefinisikan sebagai jenis data bilangan bulat apa pun. Jika jenis data tidak ditentukan, urutan akan default menjadi bigint.

Menggunakan urutan

Gunakan urutan alih-alih kolom identitas dalam skenario berikut:

• Aplikasi memerlukan angka sebelum penyisipan ke dalam tabel dibuat.

• Aplikasi ini memerlukan berbagi satu rangkaian angka antara beberapa tabel atau beberapa kolom dalam tabel.

• Aplikasi harus menghidupkan ulang seri angka ketika angka yang ditentukan tercapai. Misalnya, setelah menetapkan nilai 1 hingga 10, aplikasi mulai menetapkan nilai 1 hingga 10 lagi.

• Aplikasi memerlukan nilai urutan untuk diurutkan menurut bidang lain. Fungsi NEXT VALUE FOR dapat menerapkan klausa OVER ke panggilan fungsi. Klausul OVER menjamin bahwa nilai yang dikembalikan dihasilkan sesuai urutan yang ditentukan dalam klausa ORDER BY dari klausa OVER.

• Aplikasi mengharuskan beberapa angka ditetapkan secara bersamaan. Misalnya, aplikasi perlu mengalokasikan lima nomor berurutan. Meminta nilai identitas dapat mengakibatkan kesenjangan dalam rangkaian jika proses lain secara bersamaan mengeluarkan nomor. Memanggil sp_sequence_get_range dapat mengambil beberapa nomor dalam urutan secara bersamaan.

• Anda perlu mengubah spesifikasi urutan, seperti nilai peningkatan.

Batasan

Tidak seperti kolom identitas, yang nilainya tidak dapat diubah, nilai urutan tidak dilindungi secara otomatis setelah penyisipan ke dalam tabel. Untuk mencegah nilai urutan diubah, gunakan pemicu pembaruan pada tabel untuk mengembalikan perubahan.

Penerapan sifat unik tidak dilakukan secara otomatis untuk nilai berurutan. Kemampuan untuk menggunakan kembali nilai urutan memang dirancang. Jika nilai urutan dalam tabel harus unik, buat batasan unik pada kolom. Jika nilai urutan dalam tabel diharuskan unik di seluruh grup tabel, buat pemicu untuk mencegah duplikat yang disebabkan oleh pernyataan pembaruan data atau siklus ulang nomor urutan.

Objek urutan menghasilkan angka sesuai dengan definisinya, tetapi objek urutan tidak mengontrol bagaimana angka digunakan. Nomor urutan yang dimasukkan ke dalam tabel dapat memiliki celah saat transaksi digulung balik, ketika objek urutan dibagikan oleh beberapa tabel, atau ketika nomor urutan dialokasikan tanpa menggunakannya dalam tabel. Saat dibuat dengan opsi CACHE, pemadaman yang tidak terduga, seperti mati listrik, dapat kehilangan nomor urutan dalam cache.

Jika ada beberapa instans fungsi NEXT VALUE FOR yang menentukan generator urutan yang sama dalam satu pernyataan Transact-SQL, semua instans tersebut mengembalikan nilai yang sama untuk baris tertentu yang diproses oleh pernyataan Transact-SQL tersebut. Perilaku ini konsisten dengan standar ANSI.

Nomor urut dihasilkan di luar cakupan transaksi saat ini. Mereka terpakai apakah transaksi dengan nomor urut tersebut diselesaikan atau dibatalkan. Validasi duplikat hanya terjadi setelah catatan diisi sepenuhnya. Ini dapat mengakibatkan beberapa kasus di mana angka yang sama digunakan untuk lebih dari satu rekaman selama pembuatan, tetapi kemudian diidentifikasi sebagai duplikat. Jika hal ini terjadi dan nilai autonumber yang lain telah diterapkan ke catatan berikutnya, hal ini dapat menyebabkan kesenjangan di antara nilai autonumber.

Kegunaan umum

Untuk membuat angka urutan bilangan bulat yang bertambah 1 dari -2.147.483.648 hingga 2.147.483.647, gunakan pernyataan berikut.

CREATE SEQUENCE Schema.SequenceName
    AS int
    INCREMENT BY 1 ;

Untuk membuat nomor urut bilangan bulat yang mirip dengan kolom identitas yang bertambah 1 dari 1 hingga 2.147.483.647, gunakan pernyataan berikut.

CREATE SEQUENCE Schema.SequenceName
    AS int
    START WITH 1
    INCREMENT BY 1 ;

Mengelola rangkaian

Untuk informasi tentang urutan, kueri sys.sequences.

Contoh

Ada contoh tambahan dalam artikel CREATE SEQUENCE, NEXT VALUE FOR, dan sp_sequence_get_range.

A. Menggunakan nomor urut dalam satu tabel

Contoh berikut membuat skema bernama Test, tabel bernama Orders, dan urutan bernama CountBy1, lalu menyisipkan baris ke dalam tabel menggunakan fungsi NEXT VALUE FOR.

CREATE SCHEMA Test;
GO

CREATE TABLE Test.Orders
(
    OrderID INT PRIMARY KEY,
    Name VARCHAR (20) NOT NULL,
    Qty INT NOT NULL
);
GO

CREATE SEQUENCE Test.CountBy1
    START WITH 1
    INCREMENT BY 1;
GO

INSERT Test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Tire', 2);

INSERT test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Seat', 1);

INSERT test.Orders (OrderID, Name, Qty)
VALUES            ( NEXT VALUE FOR Test.CountBy1, 'Brake', 1);
GO

SELECT *
FROM Test.Orders;
GO

Inilah hasilnya.

OrderID Name Qty

1 Tire 2

2 Seat 1

3 Brake 1

B. Memanggil FUNGSI NILAI BERIKUT sebelum memasukkan baris

Menggunakan tabel yang Orders dibuat dalam contoh A, contoh berikut mendeklarasikan variabel bernama @nextID, lalu menggunakan fungsi NEXT VALUE FOR untuk mengatur variabel ke nomor urutan berikutnya yang tersedia. Aplikasi ini diduga melakukan beberapa pemrosesan pesanan, seperti memberi pelanggan OrderID jumlah pesanan potensial mereka, dan kemudian memvalidasi pesanan. Tidak peduli berapa lama pemrosesan ini mungkin berlangsung, atau berapa banyak pesanan lain yang ditambahkan selama proses, nomor asli dipertahankan untuk digunakan oleh koneksi ini. Akhirnya, INSERT perintah menambahkan pesanan ke tabel Orders.

DECLARE @NextID AS INT;

SET @NextID =  NEXT VALUE FOR Test.CountBy1;

INSERT Test.Orders (OrderID, Name, Qty)
VALUES            (@NextID, 'Rim', 2);

C. Menggunakan nomor urut dalam beberapa tabel

Contoh ini mengasumsikan bahwa proses pemantauan lini produksi menerima pemberitahuan peristiwa yang terjadi di seluruh lokakarya. Setiap acara menerima angka yang unik dan meningkat secara monoton EventID. Semua peristiwa menggunakan nomor urut yang sama EventID sehingga laporan yang menggabungkan semua peristiwa dapat mengidentifikasi setiap peristiwa secara unik. Namun data peristiwa disimpan dalam tiga tabel yang berbeda, tergantung pada jenis peristiwa. Contoh kode membuat skema bernama Audit, urutan bernama EventCounter, dan tiga tabel yang masing-masing menggunakan EventCounter urutan sebagai nilai default. Kemudian contoh ini menambahkan baris ke tiga tabel dan memeriksa hasilnya.

CREATE SCHEMA Audit;
GO

CREATE SEQUENCE Audit.EventCounter
    AS INT
    START WITH 1
    INCREMENT BY 1;
GO

CREATE TABLE Audit.ProcessEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EventCode NVARCHAR (5) NOT NULL,
    Description NVARCHAR (300) NULL
);
GO

CREATE TABLE Audit.ErrorEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EquipmentID INT NULL,
    ErrorNumber INT NOT NULL,
    EventDesc NVARCHAR (256) NULL
);
GO

CREATE TABLE Audit.StartStopEvents
(
    EventID INT DEFAULT ( NEXT VALUE FOR Audit.EventCounter) PRIMARY KEY CLUSTERED,
    EventTime DATETIME DEFAULT (getdate()) NOT NULL,
    EquipmentID INT NOT NULL,
    StartOrStop BIT NOT NULL
);
GO

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (248, 0);

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (72, 0);

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (2735, 'Clean room temperature 18 degrees C.');

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (18, 'Spin rate threshold exceeded.');

INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)
VALUES                  (248, 82, 'Feeder jam');

INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES                      (248, 1);

INSERT Audit.ProcessEvents (EventCode, Description)
VALUES                    (1841, 'Central feed in bypass mode.');

SELECT EventID,
       EventTime,
       Description
FROM Audit.ProcessEvents
UNION
SELECT EventID,
       EventTime,
       EventDesc
FROM Audit.ErrorEvents
UNION
SELECT EventID,
       EventTime,
       CASE StartOrStop WHEN 0 THEN 'Start' ELSE 'Stop' END
FROM Audit.StartStopEvents
ORDER BY EventID;

Berikut kumpulan hasilnya.

EventID EventTime Description

1 2009-11-02 15:00:51.157 Start

2 2009-11-02 15:00:51.160 Start

3 2009-11-02 15:00:51.167 Clean room temperature 18 degrees C.

4 2009-11-02 15:00:51.167 Spin rate threshold exceeded.

5 2009-11-02 15:00:51.173 Feeder jam

6 2009-11-02 15:00:51.177 Stop

7 2009-11-02 15:00:51.180 Central feed in bypass mode.

D. Menghasilkan angka urutan berulang dalam tataan hasil

Contoh berikut menunjukkan dua fitur dari nomor urut: siklus, dan penggunaan NEXT VALUE FOR dalam pernyataan select.

CREATE SEQUENCE CountBy5
    AS TINYINT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 5
    CYCLE;
GO

SELECT  NEXT VALUE FOR CountBy5 AS SurveyGroup,
       Name
FROM sys.objects;
GO

E. Menghasilkan angka urutan untuk hasil yang ditetapkan dengan menggunakan klausa OVER

Contoh berikut menggunakan klausul OVER untuk mengurutkan hasil yang ditetapkan sebelum Name menambahkan kolom nomor urut.

USE AdventureWorks2022;
GO

CREATE SCHEMA Samples;
GO

CREATE SEQUENCE Samples.IDLabel
    AS TINYINT
    START WITH 1
    INCREMENT BY 1;
GO

SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

F. Mereset nomor urut

Contoh E menghabiskan 79 angka urutan pertama dari Samples.IDLabel. (Versi AdventureWorks2022 Anda mungkin mengembalikan jumlah hasil yang berbeda.) Laksanakan yang berikut untuk menggunakan 79 angka urutan berikutnya (80 hingga 158).

SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

Jalankan pernyataan berikut untuk memulai ulang urutan Samples.IDLabel.

ALTER SEQUENCE Samples.IDLabel
RESTART WITH 1 ;

Jalankan pernyataan pilih lagi untuk memverifikasi bahwa Samples.IDLabel urutan dimulai ulang dengan angka 1.

SELECT  NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID,
       ProductID,
       Name,
       ProductNumber
FROM Production.Product
WHERE Name LIKE '%nut%';

G. Mengubah tabel dari identitas ke urutan

Contoh berikut membuat skema dan tabel yang berisi tiga baris untuk contoh. Kemudian contoh menambahkan kolom baru dan menghilangkan kolom lama.

CREATE SCHEMA Test;
GO

CREATE TABLE Test.Department
(
    DepartmentID SMALLINT IDENTITY (1, 1) NOT NULL,
    Name NVARCHAR (100) NOT NULL,
    GroupName NVARCHAR (100) NOT NULL CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED (DepartmentID ASC)
);
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Engineering', 'Research and Development');
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Tool Design', 'Research and Development');
GO

INSERT Test.Department (Name, GroupName)
VALUES                ('Sales', 'Sales and Marketing');
GO

SELECT *
FROM Test.Department;
GO

ALTER TABLE Test.Department
    ADD DepartmentIDNew SMALLINT NULL;
GO

UPDATE Test.Department
    SET DepartmentIDNew = DepartmentID;
GO

ALTER TABLE Test.Department DROP CONSTRAINT [PK_Department_DepartmentID];

ALTER TABLE Test.Department DROP COLUMN DepartmentID;
GO

EXECUTE sp_rename 'Test.Department.DepartmentIDNew', 'DepartmentID', 'COLUMN';
GO

ALTER TABLE Test.Department ALTER COLUMN DepartmentID SMALLINT NOT NULL;

ALTER TABLE Test.Department
    ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED (DepartmentID ASC);

SELECT MAX(DepartmentID)
FROM Test.Department;

CREATE SEQUENCE Test.DeptSeq
    AS SMALLINT
    START WITH 4
    INCREMENT BY 1;
GO

ALTER TABLE Test.Department
    ADD CONSTRAINT DefSequence DEFAULT ( NEXT VALUE FOR Test.DeptSeq) FOR DepartmentID;
GO

SELECT DepartmentID,
       Name,
       GroupName
FROM Test.Department;

INSERT Test.Department (Name, GroupName)
VALUES                ('Audit', 'Quality Assurance');
GO

SELECT DepartmentID,
       Name,
       GroupName
FROM Test.Department;

Pernyataan Transact-SQL yang menggunakan SELECT * akan menerima kolom baru sebagai kolom terakhir alih-alih kolom pertama. Jika ini tidak dapat diterima, maka Anda harus membuat tabel yang sama sekali baru, memindahkan data ke tabel tersebut, lalu membuat ulang izin pada tabel baru.

Konten terkait

• CREATE SEQUENCE (Transact-SQL)
• ALTER SEQUENCE (Transact-SQL)
• DROP SEQUENCE (Transact-SQL)
• CREATE TABLE (Transact-SQL) IDENTITY (Properti)