Trong bài viết này, mình sẽ chia sẻ tới các bạn cách tạo và làm việc với cột định danh (hay cột nhận dạng) bằng cách sử dụng thuộc tính IDENTITY và đồng thời so sánh IDENTITY với SEQUENCE. Hiểu một cách đơn giản, chúng ta sẽ đi tạo tự động một chuỗi giá trị tuần tự cho một cột trong bảng.  

IDENTITY

Để sử dụng thuộc tính IDENTITY, hãy xác định một cột bằng kiểu dữ liệu số với tỷ lệ 0 (nghĩa là chỉ các số nguyên) và bao gồm từ khóa IDENTITY. Trong cú pháp của IDENTITY, bạn cần xác định được: 

• Seed: giá trị bắt đầu của chuỗi (giá trị của hàng đầu tiên được insert vào bảng) 
• Increment: giá trị tăng thêm ở dòng kế tiếp 

Giá trị seed và increment mặc định là 1. 

Lưu ý: Thuộc tính IDENTITY được chỉ định thay vì chỉ định NOT hoặc NOT NULL trong định nghĩa cột. Bất kỳ cột nào có thuộc tính IDENTITY sẽ tự động không thể chứa NULL.  

Chỉ một cột trong bảng có thể có thuộc tính IDENTITY; nó thường được sử dụng như một PRIMARY KEY hoặc một key thay thế. 

Đoạn code sau cho thấy việc tạo bảng Sales.Promotion được sử dụng trong các ví dụ phần trước, nhưng lần này với một cột nhận dạng có tên là PromotionID làm khóa chính: 

CREATE TABLE Sales.Promotion  

(  

PromotionID int IDENTITY PRIMARY KEY,  

PromotionName varchar(20),  

StartDate datetime NOT NULL DEFAULT GETDATE(),  

ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),  

Discount decimal(4,2) NOT NULL,  

Notes nvarchar(max) NULL  

); 

Chèn dữ liệu vào cột identity

Khi một thuộc tính IDENTITY được xác định cho một cột, các câu lệnh INSERT vào bảng thường không chỉ định giá trị cho cột IDENTITY. Công cụ cơ sở dữ liệu tạo ra một giá trị bằng cách sử dụng giá trị có sẵn tiếp theo cho cột. 

Ví dụ: bạn có thể chèn một hàng vào bảng Sales.Promotion mà không cần chỉ định giá trị cho cột PromotionID: 

INSERT INTO Sales.Promotion 

VALUES 

('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount') 

Lưu ý rằng mặc dù mệnh đề VALUES không bao gồm giá trị cho cột PromotionID, bạn không cần chỉ định danh sách cột trong mệnh đề INSERT – Các cột IDENTITY được miễn yêu cầu này. 

Nếu hàng này là hàng đầu tiên được chèn vào bảng, kết quả là một hàng mới như sau: 

Khi bảng được tạo, không có giá trị gốc hoặc giá trị gia tăng nào được đặt cho cột IDENTITY, vì vậy hàng đầu tiên được chèn với giá trị là 1. Hàng tiếp theo được chèn sẽ được gán giá trị PromotionID là 2, v.v. 

Truy xuất giá trị identity

Để trả về giá trị IDENTITY được chỉ định gần đây nhất trong cùng một phiên và phạm vi, hãy sử dụng hàm SCOPE_IDENTITY; như thế này: 

SELECT SCOPE_IDENTITY()

Hàm SCOPE_IDENTITY trả về identity gần nhất được tạo trong phạm vi hiện tại cho bất kỳ bảng nào. Nếu bạn cần giá trị identity mới nhất trong một bảng cụ thể, bạn có thể sử dụng hàm IDENT_CURRENT, như sau: 

SELECT IDENT_CURRENT('Sales.Promotion')

Ghi đè các giá trị identity 

Nếu bạn muốn ghi đè giá trị được tạo tự động và gán một giá trị cụ thể cho cột IDENTITY, trước tiên bạn cần bật chèn danh tính bằng cách sử dụng câu lệnh SET IDENTITY INSERT table_name ON. Với tùy chọn này được bật, bạn có thể chèn một giá trị rõ ràng cho cột danh tính, giống như bất kỳ cột nào khác. Khi hoàn tất, bạn có thể sử dụng câu lệnh SET IDENTITY INSERT table_name OFF để tiếp tục sử dụng các identity tự động. 

SET IDENTITY_INSERT SalesLT.Promotion ON;  

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)  

VALUES  

(20, 'Another short sale',37, 0.3);  

SET IDENTITY_INSERT SalesLT.Promotion OFF; 

SEQUENCE 

Như bạn đã học, thuộc tính IDENTITY được sử dụng để tạo chuỗi giá trị cho một cột trong bảng. Tuy nhiên, thuộc tính IDENTITY không phù hợp để phối hợp các giá trị trên nhiều bảng trong cơ sở dữ liệu. Ví dụ: giả sử doanh nghiệp mà bạn đang làm việc phân biệt giữa bán hàng trực tiếp và bán hàng cho người bán lại và muốn lưu trữ dữ liệu cho các lần bán hàng này trong các bảng riêng biệt. Cả hai hình thức bán hàng có thể cần một số hóa đơn duy nhất và bạn có thể muốn tránh trùng lặp cùng một giá trị cho hai hình thức bán hàng khác nhau. Một giải pháp cho yêu cầu này là duy trì một nhóm các giá trị tuần tự duy nhất trên cả hai bảng. 

Trong Transact-SQL, bạn có thể sử dụng một đối tượng trình tự để cung cấp các giá trị tuần tự mới độc lập với một bảng cụ thể. Một đối tượng chuỗi được tạo bằng cách sử dụng câu lệnh CREATE SEQUENCE, tùy chọn cung cấp kiểu dữ liệu (phải là kiểu số nguyên hoặc số thập phân hoặc số với tỷ lệ 0), giá trị bắt đầu, giá trị gia tăng, giá trị tối đa và các tùy chọn khác liên quan đến màn biểu diễn. 

CREATE SEQUENCE Sales.InvoiceNumber AS INT

START WITH 1000 INCREMENT BY 1;

Để lấy được giá trị tiếp theo từ chuỗi, ta sử dụng cú pháp NEXT VALUE FOR như sau:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY hay SEQUENCE?

Khi quyết định sử dụng cột IDENTITY hay đối tượng SEQUENCE cho các giá trị tự động điền, hãy ghi nhớ những điểm sau: 

• Sử dụng SEQUENCE nếu ứng dụng của bạn yêu cầu chia sẻ một chuỗi số giữa nhiều bảng hoặc nhiều cột trong một bảng. 
• SEQUENCE cho phép bạn sắp xếp các giá trị theo một cột khác. Cấu trúc NEXT VALUE FOR có thể sử dụng mệnh đề OVER để chỉ định cột sắp xếp. Mệnh đề OVER đảm bảo rằng các giá trị trả về được tạo ra theo thứ tự của mệnh đề ORDER BY của mệnh đề OVER. Chức năng này cũng cho phép bạn tạo số hàng cho các hàng khi chúng được trả về trong một SELECT. Trong ví dụ sau, bảng Production.Product được sắp xếp theo cột Tên và cột trả về đầu tiên là một số thứ tự.

SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name

FROM Production.Product;

Lưu ý: Mặc dù câu lệnh trước đó chỉ chọn các giá trị SEQUENCE để hiển thị, các giá trị vẫn đang được ‘sử dụng hết’ và các giá trị SEQUENCE được hiển thị sẽ không còn nữa. Nếu bạn chạy SELECT ở trên nhiều lần, bạn sẽ nhận được các giá trị SEQUENCE khác nhau mỗi lần. 

• Sử dụng SEQUENCE nếu ứng dụng của bạn yêu cầu nhiều số được chỉ định cùng một lúc. Ví dụ, một ứng dụng cần đặt trước năm số liên tiếp. Yêu cầu giá trị nhận dạng có thể dẫn đến khoảng trống trong chuỗi nếu các quy trình khác được cấp số đồng thời. Bạn có thể sử dụng quy trình hệ thống sp_sequence_get_range để truy xuất một số số trong dãy cùng một lúc. 
• SEQUENCE cho phép bạn thay đổi đặc điểm kỹ thuật của trình tự, chẳng hạn như increment. 
• Các giá trị IDENTITY được bảo vệ khỏi các bản cập nhật. Nếu bạn cố gắng cập nhật một cột có thuộc tính IDENTITY, bạn sẽ gặp lỗi. 

Các bạn có thể xem thêm các bài viết khác của mình tại đây.

Ngoài ra, các bạn có thể tham khảo các khóa học do mình cùng cộng sự giảng dạy ở Datapot tại đây để được hướng dẫn chi tiết và bài bản các kỹ năng cần thiết của một DA nhé!