Nomor Urut

Artikel
07/20/2022
9 menit untuk membaca

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance

Urutan adalah objek terikat skema yang ditentukan pengguna yang menghasilkan urutan nilai numerik sesuai dengan spesifikasi tempat urutan dibuat. Urutan nilai numerik dihasilkan dalam urutan naik atau turun pada interval yang ditentukan dan dapat berputar (berulang) 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 dikendalikan 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 mulai ulang otomatis, dan penembolokan untuk meningkatkan performa. Untuk informasi tentang opsi, lihat CREATE SEQUENCE.

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 default ke bigint.

Menggunakan Urutan

Gunakan urutan alih-alih kolom identitas dalam skenario berikut:

Aplikasi memerlukan angka sebelum sisipan ke dalam tabel dibuat.
Aplikasi ini memerlukan berbagi satu rangkaian angka antara beberapa tabel atau beberapa kolom dalam tabel.
Aplikasi harus memulai 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 dalam urutan klausul ORDER BY klausul OVER.
Aplikasi memerlukan beberapa angka 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. Memanggil sp_sequence_get_range dapat mengambil beberapa nomor dalam urutan sekaligus.
Anda perlu mengubah spesifikasi urutan, seperti nilai kenaikan.

Batasan

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

Keunikan tidak secara otomatis diberlakukan untuk nilai urutan. Kemampuan untuk menggunakan kembali nilai urutan adalah berdasarkan desain. Jika nilai urutan dalam tabel harus unik, buat indeks unik pada kolom. Jika nilai urutan dalam tabel harus unik di seluruh grup tabel, buat pemicu untuk mencegah duplikat yang disebabkan oleh pernyataan pembaruan atau bersepeda nomor urut.

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, saat objek urutan dibagikan oleh beberapa tabel, atau saat nomor urutan dialokasikan tanpa menggunakannya dalam tabel. Saat dibuat dengan opsi CACHE, matikan yang tidak terduga, seperti kegagalan daya, dapat kehilangan nomor urut 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 dikonsumsi apakah transaksi menggunakan nomor urut diterapkan atau digulung balik. Validasi duplikat hanya terjadi setelah rekaman 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 ini terjadi dan nilai autonumber lainnya telah diterapkan ke rekaman berikutnya, ini dapat mengakibatkan kesenjangan antara nilai autonumber.

Penggunaan Umum

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

CREATE SEQUENCE Schema.SequenceName  
    AS int  
    INCREMENT BY 1 ;

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

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

Mengelola Urutan

Untuk informasi tentang urutan, kueri sys.sequences.

Contoh

Ada contoh tambahan dalam topik CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL), dan sp_sequence_get_range.

J. 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 the Test schema  
CREATE SCHEMA Test ;  
GO  
  
-- Create a table  
CREATE TABLE Test.Orders  
    (OrderID int PRIMARY KEY,  
    Name varchar(20) NOT NULL,  
    Qty int NOT NULL);  
GO  
  
-- Create a sequence  
CREATE SEQUENCE Test.CountBy1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
  
-- Insert three records  
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  
  
-- View the table  
SELECT * FROM Test.Orders ;  
GO

Berikut adalah tataan hasil.

OrderID Name Qty

1 Tire 2

2 Seat 1

3 Brake 1

B. Memanggil NEXT VALUE FOR sebelum menyisipkan 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 dianggap 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 pernyataan menambahkan pesanan ke Orders tabel.

DECLARE @NextID int ;  
SET @NextID = NEXT VALUE FOR Test.CountBy1;  
-- Some work happens  
INSERT Test.Orders (OrderID, Name, Qty)  
    VALUES (@NextID, 'Rim', 2) ;  
GO

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 EventID secara monoton. Semua peristiwa menggunakan nomor urutan 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 menambahkan baris ke tiga tabel dan mengkueri hasilnya.

CREATE SCHEMA Audit ;  
GO  
CREATE SEQUENCE Audit.EventCounter  
    AS int  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  
  
CREATE TABLE Audit.ProcessEvents  
(  
    EventID int PRIMARY KEY CLUSTERED   
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EventCode nvarchar(5) NOT NULL,  
    Description nvarchar(300) NULL  
) ;  
GO  
  
CREATE TABLE Audit.ErrorEvents  
(  
    EventID int PRIMARY KEY CLUSTERED  
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    EquipmentID int NULL,  
    ErrorNumber int NOT NULL,  
    EventDesc nvarchar(256) NULL  
) ;  
GO  
  
CREATE TABLE Audit.StartStopEvents  
(  
    EventID int PRIMARY KEY CLUSTERED  
        DEFAULT (NEXT VALUE FOR Audit.EventCounter),  
    EventTime datetime NOT NULL DEFAULT (getdate()),  
    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 threashold 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.') ;  
-- The following statement combines all events, though not all fields.  
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 ;  
GO

Berikut adalah hasil yang ditetapkan.

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 nomor urutan berulang dalam tataan hasil

Contoh berikut menunjukkan dua fitur nomor urut: bersepeda, dan menggunakan NEXT VALUE FOR dalam pernyataan pilih.

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 nomor urut 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 AdventureWorks2012 ;  
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 menggunakan 79 pertama dari Samples.IDLabel nomor urut. (Versi AdventureWorks2012 Anda mungkin mengembalikan jumlah hasil yang berbeda.) Jalankan yang berikut ini untuk mengonsumsi 79 nomor urutan berikutnya (80 meskipun 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 Samples.IDLabel urutan.

ALTER SEQUENCE Samples.IDLabel  
RESTART WITH 1 ;

Jalankan pernyataan pilih lagi untuk memverifikasi bahwa Samples.IDLabel urutan dimulai ulang dengan nomor 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 a schema  
CREATE SCHEMA Test ;  
GO  
  
-- Create a table  
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 three rows into the table  
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  
  
-- View the table that will be changed  
SELECT * FROM Test.Department ;  
GO  
  
-- End of portion creating a sample table  
--------------------------------------------------------  
-- Add the new column that does not have the IDENTITY property  
ALTER TABLE Test.Department   
    ADD DepartmentIDNew smallint NULL  
GO  
  
-- Copy values from the old column to the new column  
UPDATE Test.Department  
    SET DepartmentIDNew = DepartmentID ;  
GO  
  
-- Drop the primary key constraint on the old column  
ALTER TABLE Test.Department  
    DROP CONSTRAINT [PK_Department_DepartmentID];  
-- Drop the old column  
ALTER TABLE Test.Department  
    DROP COLUMN DepartmentID ;  
GO  
  
-- Rename the new column to the old columns name  
EXEC sp_rename 'Test.Department.DepartmentIDNew',   
    'DepartmentID', 'COLUMN';  
GO  
  
-- Change the new column to NOT NULL  
ALTER TABLE Test.Department  
    ALTER COLUMN DepartmentID smallint NOT NULL ;  
-- Add the unique primary key constraint  
ALTER TABLE Test.Department  
    ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED   
         (DepartmentID ASC) ;  
-- Get the highest current value from the DepartmentID column   
-- and create a sequence to use with the column. (Returns 3.)  
SELECT MAX(DepartmentID) FROM Test.Department ;  
-- Use the next desired value (4) as the START WITH VALUE;  
CREATE SEQUENCE Test.DeptSeq  
    AS smallint  
    START WITH 4  
    INCREMENT BY 1 ;  
GO  
  
-- Add a default value for the DepartmentID column  
ALTER TABLE Test.Department  
    ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR Test.DeptSeq)   
        FOR DepartmentID;  
GO  
  
-- View the result  
SELECT DepartmentID, Name, GroupName  
FROM Test.Department ;   
-- Test insert  
INSERT Test.Department (Name, GroupName)  
    VALUES ('Audit', 'Quality Assurance') ;  
GO  
  
-- View the result  
SELECT DepartmentID, Name, GroupName  
FROM Test.Department ;  
GO

Pernyataan transact-SQL yang menggunakan SELECT * akan menerima kolom baru sebagai kolom terakhir, bukan 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.

CREATE SEQUENCE (Transact-SQL)

ALTER SEQUENCE (Transact-SQL)

DROP SEQUENCE (Transact-SQL)

IDENTITY (Properti) (transact-SQL)