SEQUENCE trong SQL Server

Sequence là một tập hợp các số nguyên được tạo thứ tự theo yêu cầu. Các sequence thường được sử dụng trong cơ sở dữ liệu vì đáp ứng yêu cầu của nhiều ứng dụng là đòi hỏi mỗi hàng trong một bảng chứa một giá trị duy nhất tương tự như khóa chính.

Bài viết sẽ cung cấp cho bạn cú pháp và những ví dụ về cách tạo và xóa bỏ sequence trong SQL Server.

CREATE SEQUENCE (Tạo Sequence)

Cú pháp

Để tạo một sequence, ta có cú pháp sau:

CREATE SEQUENCE [schema.]sequence_name
 [ AS datatype ]
 [ START WITH value ]
 [ INCREMENT BY value ]
 [ MINVALUE value | NO MINVALUE ]
 [ MAXVALUE value | NO MAXVALUE ]
 [ CYCLE | NO CYCLE ]
 [ CACHE value | NO CACHE ];

Tham số:

• AS datatype: có thể là các kiểu BIGINT, INT, TINYINT, SMALLINT, DECIMAL, or NUMERIC. Nếu không chỉ định một kiểu cụ thể, chương trình sẽ mặc định datatype của bạn ở dạng BIGINT.
• START WITH value: Giá trị bắt đầu mà sequence trả về.
• INCREMENT BY value: Quy luật tăng/giảm của sequence, có thể mang giá trị dương hoặc âm. Nếu value ở đây là dương, sequence sẽ là một chuỗi các giá trị tăng dần. Ngược lại là âm, sequence sẽ giảm dần.
• MINVALUE value: Giá trị nhỏ nhất trong chuỗi.
• NO MINVALUE: Không chỉ định giá trị nhỏ nhất.
• MAXVALUE value: Giá trị lớn nhất trong chuỗi.
• NO MAXVALUE: Không chỉ định giá trị lớn nhất.
• CYCLE: Sequence sẽ bắt đầu lại từ đầu khi nó hoàn thành chuỗi.
• NO CYCLE: Sequence sẽ xảy ra lỗi khi kết thúc chuỗi, nó sẽ không bắt đầu lại khi hoàn thành.
• CACHE value: Lưu ở bộ nhớ đệm (cache) để giảm thiểu cho disk IO.
• NO CACHE: Không lưu ở cache.

Ví dụ

CREATE SEQUENCE contacts_seq
 AS BIGINT
 START WITH 1
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 99999
 NO CYCLE
 CACHE 10;

Ở đây ta đã tạo một sequence có tên là contacts_seq, bắt đầu từ giá trị 1, mỗi giá trị sau tăng thêm một đơn vị (tức là 2, 3, 4…). Chuỗi sẽ lưu khoảng 10 giá trị vào bộ nhớ cache. Giá trị lớn nhất trong sequence là 99999 và nó sẽ không bắt đầu lại chuỗi sau khi sequence kết thúc ở giá trị lớn nhất.

Bạn cũng có thể thực hiện lệnh trên một cách đơn giản như sau:

CREATE SEQUENCE contacts_seq
 START WITH 1
  INCREMENT BY 1;

Vậy là vừa xong bạn đã tạo sequence mô phỏng một trường autonumber. Tiếp theo, để xuất một giá trị từ dãy này ta sử dụng lệnh NEXT VALUE FOR

SELECT NEXT VALUE FOR contacts_seq;

Câu lệnh này sẽ lấy giá trị tiếp theo từ contacts_seq. Tiếp đó sử dụng các câu lệnh cần thiết cho việc thực thi của bạn. Ví dụ:

INSERT INTO contacts
 (contact_id, last_name)
VALUES
  (NEXT VALUE FOR contacts_seq, 'Smith');

Câu lệnh INSERT này sẽ chèn một bản ghi mới vào bảng contact. Trường contact_id sẽ được gán số tiếp theo từ chuỗi contacts_seq, trường last_name sẽ là ‘Smith’.

DROP SEQUENCE (Xóa bỏ Sequence)

Một khi đã tạo thành công các sequence thì cũng sẽ có những trường hợp bạn muốn xóa bỏ sequence khỏi cơ sở dữ liệu vì một vài lý do.

Cú pháp

Để xóa bỏ một sequence, ta có cú pháp sau:

DROP SEQUENCE sequence_name;

Tham số:

sequence_name: Tên sequence bạn muốn xóa bỏ.

Ví dụ

DROP SEQUENCE contacts_seq;

Thực hiện lệnh này là bạn đã vừa xóa bỏ chuỗi contacts_seq khỏi database.

Thuộc tính của Sequence

Để kiểm tra thuộc tính của sequence, ta có cú pháp sau:

SELECT * FROM sys.sequences WHERE name = 'sequence_name';

Tham số:

sequence_name: Tên chuỗi muốn kiểm tra thuộc tính.

Ví dụ

SELECT *
FROM sys.sequences
WHERE name = 'contacts_seq';

Ví dụ này truy vấn thông tin từ hệ thống sys.sequences và truy xuất kết quả cho chuỗi contacts_seq.

Hệ thống sys.sequences gồm các cột sau:

CỘT	CHÚ THÍCH
name	Tên sequence đã được tạo ở câu lệnh CREATE SEQUENCE
object_id	ID của đối tượng
principal_id	ID principal của sequence (giá trị số)
schema_id	ID Schema của sequence
parent_object_id	ID của đối tượng cha
type	SO
type_desc	SEQUENCE_OBJECT
create_date	Ngày/Thời gian tạo sequence bằng lệnh CREATE SEQUENCE
modify_date	Ngày/Thời gian cuối cùng chỉnh sửa sequence
is_ms_shipped	Giá trị 0 hoặc 1
is_published	Giá trị 0 hoặc 1
is_schema_published	Giá trị 0 hoặc 1
start_value	Giá trị bắt đầu của sequence
increment	Giá trị quy luật tăng/giảm của sequence
minimum_value	Giá trị nhỏ nhất trong chuỗi
maximum_value	Giá trị lớn nhất trong chuỗi
is_cycling	Giá trị 0 hoặc 1. 0=NO CYCLE, 1=CYCLE
is_cached	Giá trị 0 hoặc 1, 0=NO CACHE, 1=CACHE
cache_size	Kích thước của bộ nhớ đệm khi is_cached = 1
system_type_id	ID system của sequence
user_type_id	ID loại user của sequence
precision	Độ chính xác tối đa cho kiểu dữ liệu của sequence
scale	Phạm vi tối đa cho kiểu dữ liệu của sequence
current_value	Giá trị cuối cùng được truy xuất từ sequence
is_exhausted	Giá trị 0 hoặc 1. 0=Có nhiều giá trị trong sequence. 1=Không có giá trị