Nomor Urut
Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Urutannya adalah objek terikat skema yang ditentukan pengguna yang menghasilkan urutan nilai numerik sesuai dengan spesifikasi yang urutannya 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 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 mulai ulang otomatis, dan penembolokan 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 default ke 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 dalam urutan klausul ORDER BY klausul OVER.
Aplikasi mengharuskan beberapa angka 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 dilindungi secara otomatis setelah penyisipan ke dalam tabel. Untuk mencegah nilai urutan diubah, gunakan pemicu pembaruan pada tabel untuk mengembalikan perubahan.
Keunikan tidak diberlakukan secara otomatis untuk nilai urutan. Kemampuan untuk menggunakan kembali nilai urutan adalah dengan desain. 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 atau nomor urutan bersepeda.
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, pematian yang tidak terduga, seperti kegagalan daya, 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 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 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 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 hasil yang ditetapkan.
OrderID Name Qty
1 Tire 2
2 Seat 1
3 Brake 1
B. Memanggil NILAI BERIKUTNYA UNTUK 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 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
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 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 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 angka 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 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 menggunakan 79 Samples.IDLabel
angka urutan pertama. (Versi AdventureWorks2022
Anda mungkin mengembalikan jumlah hasil yang berbeda.) Jalankan yang berikut untuk menggunakan angka urutan 79 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 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 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 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
Saran dan Komentar
https://aka.ms/ContentUserFeedback.
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat:Kirim dan lihat umpan balik untuk