Sequence trong SQL Server

Trong hướng dẫn này, bạn sẽ tìm hiểu về các đối tượng Sequence trong SQL Server để tạo ra một chuỗi các giá trị số dựa trên một đặc tả cụ thể.

Sequence là gì?

Sequence chỉ đơn giản là một danh sách các số, trong đó thứ tự của chúng là quan trọng. Ví dụ: {1,2,3} là một chuỗi trong khi {3,2,1} là một chuỗi hoàn toàn khác.

Trong SQL Server, sequence là một đối tượng do người dùng định nghĩa tạo ra một chuỗi số theo một đặc điểm kỹ thuật được chỉ định. Một chuỗi các giá trị số có thể theo thứ tự tăng dần hoặc giảm dần trong một khoảng thời gian xác định và có thể quay vòng nếu được yêu cầu.

Câu lệnh CREATE SEQUENCE trong SQL Server

Để tạo một đối tượng sequence mới, bạn sử dụng câu lệnh CREATE SEQUENCE như sau:

CREATE SEQUENCE [schema_name.] sequence_name  
    [ AS integer_type ]  
    [ START WITH start_value ]  
    [ INCREMENT BY increment_value ]  
    [ { MINVALUE [ min_value ] } | { NO MINVALUE } ]  
    [ { MAXVALUE [ max_value ] } | { NO MAXVALUE } ]  
    [ CYCLE | { NO CYCLE } ]  
    [ { CACHE [ cache_size ] } | { NO CACHE } ];

Hãy xem xét cú pháp chi tiết:

sequence_name

Chỉ định tên cho sequence trong cơ sở dữ liệu hiện tại.

AS integer_type

Sử dụng bất kỳ kiểu số nguyên cho sequence ví dụ: TINYINT, SMALLINT, INT, BIGINT hay DECIMAL và NUMERIC với phần thập phân là 0. Theo mặc định, đối tượng sequence sử dụng kiểu BIGINT.

START WITH start_value

Chỉ định giá trị đầu tiên mà chuỗi trả về. Giá trị của start_value phải nằm giữa phạm vi (min_value, max_value).

Giá trị start_value mặc định là min_value theo trình tự tăng dần và max_value theo trình tự giảm dần.

INCREMENT BY increment_value

Chỉ định giá trị increment_value của đối tượng sequence khi bạn gọi hàm NEXT VALUE FOR.

Nếu giá trị increment_value là số âm, đối tượng sequence giảm dần; ngược lại, đối tượng sequence tăng dần. Lưu ý rằng giá trị increment_value không thể là 0.

[ MINVALUE min_value | NO MINVALUE ]

Chỉ định giới hạn dưới cho đối tượng sequence. Nó được đặt mặc định là giá trị nhỏ nhất của kiểu dữ liệu của đối tượng sequence, tức là, giá trị 0 cho kiểu TINYINT và một số âm cho tất cả các kiểu dữ liệu khác.

[ MAXVALUE max_value | NO MAXVALUE]

Chỉ định giới hạn trên cho đối tượng sequence. Nó mặc định là giá trị lớn nhất của kiểu dữ liệu của đối tượng sequence.

[ CYCLE | NO CYCLE ]

Sử dụng CYCLE nếu bạn muốn giá trị của đối tượng sequence khởi động lại từ min_value đối với  đối tượng sequence tăng dần, hoặc max_value đối với đối tượng sequence giảm dần hoặc ném một ngoại lệ khi vượt quá min_value hoặc max_value. SQL Server sử dụng NO CYCLE theo mặc định cho các đối tượng sequence mới.

[ CACHE cache_size ] | NO CACHE ]

Chỉ định số lượng giá trị vào bộ nhớ đệm để cải thiện hiệu suất của sequence bằng cách giảm thiểu số lượng I/O đĩa cần thiết để tạo số thứ tự. Theo mặc định, SQL Server sử dụng NO CACHE cho các đối tượng sequence mới.

Ví dụ về Sequence trong SQL Server

Hãy lấy một số ví dụ về việc tạo sequence.

1. Tạo một sequence đơn giản

Câu lệnh sau sử dụng câu lệnh CREATE SEQUENCE để tạo một chuỗi mới được đặt tên item_counter với kiểu số nguyên (INT), bắt đầu từ 10 và tăng dần 10:

CREATE SEQUENCE item_counter
    AS INT
    START WITH 10
    INCREMENT BY 10;

Bạn có thể xem đối tượng trình tự trong Programmability > Sequences như trong hình sau:

Câu lệnh sau trả về giá trị hiện tại của sequence item_counter:

SELECT NEXT VALUE FOR item_counter;

Đây là kết quả:

Current_value
-------------
10

(1 row affected)

Trong ví dụ này, hàm NEXT VALUE FOR tạo một số thứ tự từ đối tượng sequence item_counter.

Mỗi lần bạn thực hiện lại câu lệnh sau, bạn sẽ thấy rằng giá trị của đối tượng sequence item_counter sẽ được tăng thêm 10:

SELECT NEXT VALUE FOR item_counter;

Lần này đầu ra là:

Current_value
-------------
20

(1 row affected)

2. Sử dụng một đối tượng sequence trong một bảng

Đầu tiên, hãy tạo một lược đồ mới có tên là procurement:

CREATE SCHEMA procurement;
GO

Tiếp theo, tạo một bảng mới có tên là procurement.purchase_orders:

CREATE TABLE procurement.purchase_orders(
    order_id INT PRIMARY KEY,
    vendor_id int NOT NULL,
    order_date date NOT NULL
);

Sau đó, tạo một đối tượng sequence mới có tên order_number bắt đầu bằng 1 và được tăng thêm 1:

CREATE SEQUENCE procurement.order_number 
AS INT
START WITH 1
INCREMENT BY 1;

Sau đó, insert ba hàng vào bảng procurement.purchase_orders và sử dụng các giá trị được tạo bởi chuỗi procurement.order_number:

INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,1,'2019-04-30');


INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,2,'2019-05-01');


INSERT INTO procurement.purchase_orders
    (order_id,
    vendor_id,
    order_date)
VALUES
    (NEXT VALUE FOR procurement.order_number,3,'2019-05-02');

Cuối cùng, hãy xem nội dung của bảng procurement.purchase_orders:

SELECT 
    order_id, 
    vendor_id, 
    order_date
FROM 
    procurement.purchase_orders;

Đây là kết quả:

3. Sử dụng một đối tượng sequence trong nhiều bảng

Đầu tiên, tạo một đối tượng sequence mới:

CREATE SEQUENCE procurement.receipt_no
START WITH 1
INCREMENT BY 1;

Thứ hai, tạo hai bảng procurement.goods_receipts và procurement.invoice_receipts:

CREATE TABLE procurement.goods_receipts
(
    receipt_id   INT	PRIMARY KEY 
        DEFAULT (NEXT VALUE FOR procurement.receipt_no), 
    order_id     INT NOT NULL, 
    full_receipt BIT NOT NULL,
    receipt_date DATE NOT NULL,
    note NVARCHAR(100),
);


CREATE TABLE procurement.invoice_receipts
(
    receipt_id   INT PRIMARY KEY
        DEFAULT (NEXT VALUE FOR procurement.receipt_no), 
    order_id     INT NOT NULL, 
    is_late      BIT NOT NULL,
    receipt_date DATE NOT NULL,
    note NVARCHAR(100)
);

Lưu ý: cả hai bảng đều có cột receipt_id mà giá trị của nó được lấy từ sequence procurement.receipt_no.

Thứ ba, insert một số hàng vào cả hai bảng mà không cung cấp giá trị cho cột receipt_id:

INSERT INTO procurement.goods_receipts(
    order_id, 
    full_receipt,
    receipt_date,
    note
)
VALUES(
    1,
    1,
    '2019-05-12',
    'Goods receipt completed at warehouse'
);
INSERT INTO procurement.goods_receipts(
    order_id, 
    full_receipt,
    receipt_date,
    note
)
VALUES(
    1,
    0,
    '2019-05-12',
    'Goods receipt has not completed at warehouse'
);

INSERT INTO procurement.invoice_receipts(
    order_id, 
    is_late,
    receipt_date,
    note
)
VALUES(
    1,
    0,
    '2019-05-13',
    'Invoice duly received'
);
INSERT INTO procurement.invoice_receipts(
    order_id, 
    is_late,
    receipt_date,
    note
)
VALUES(
    2,
    0,
    '2019-05-15',
    'Invoice duly received'
);

Cuối cùng, truy vấn dữ liệu từ cả hai bảng:

SELECT * FROM procurement.goods_receipts;
SELECT * FROM procurement.invoice_receipts;

Đây là kết quả:

Đối tượng sequence so với cột identity

Đối tượng sequence khác với cột identity ở chỗ nó không được liên kết với một bảng. Mối quan hệ giữa đối tượng sequence và bảng được kiểm soát bởi các ứng dụng. Ngoài ra, một đối tượng sequence có thể được chia sẻ trên nhiều bảng.

Bảng sau đây minh họa sự khác biệt chính giữa sequence và cột identity:

Khi nào sử dụng Sequence

Bạn sử dụng một đối tượng sequence thay vì một cột identity trong các trường hợp sau:

• Ứng dụng yêu cầu một số trước khi insert giá trị vào bảng.
• Ứng dụng yêu cầu chia sẻ một sequence trên nhiều bảng hoặc nhiều cột trong cùng một bảng.
• Ứng dụng yêu cầu khởi động lại số khi đạt đến giá trị được chỉ định.
• Ứng dụng yêu cầu nhiều số được gán cùng một lúc. Lưu ý rằng bạn có thể gọi stored procedure sp_sequence_get_range để truy xuất một vài số trong một sequence cùng một lúc.
• Ứng dụng cần thay đổi đặc tả kỹ thuật của sequence như giá trị lớn nhất.

Nhận thông tin sequence

Bạn sử dụng view sys.sequences để lấy thông tin chi tiết của các sequence.

SELECT 
    * 
FROM 
    sys.sequences;

Trong hướng dẫn này, bạn đã học về đối tượng sequence trong SQL Server để tạo chuỗi số theo một đặc tả cụ thể.