Tạo Ràng Buộc Duy Nhất Bằng Filtered Index trong SQL server 2008

Ràng buộc duy nhất (unique constraint) được xây dựng trong SQL Server hơi khác so với định nghĩa trong lý thuyết CSDL. Theo định nghĩa (và cũng được qui định trong chuẩn ANSI) thì ràng buộc duy nhất yêu cầu các giá trị phải khác nhau trừ khi chúng là NULL.

Ví dụ nếu một bảng có 1000 bản ghi, và trường C có 800 bản ghi có các giá trị khác nhau, còn ở 200 bản ghi còn lại đều là NULL, thì vẫn thỏa mãn ràng buộc duy nhất.
SQL Server cũng yêu cầu các giá trị phải khác nhau nhưng đồng thời chỉ cho phép tối đa một giá trị NULL. Nói cách khác là nó coi NULL như một giá trị thông thường và hai bản ghi cùng NULL sẽ bị coi là trùng nhau:

CREATE TABLE dbo.CaSy(
CaSy_ID INT IDENTITY PRIMARY KEY,
Ten NVARCHAR(100),
Email VARCHAR(100),
CONSTRAINT UC_CaSy UNIQUE (Email)
)
GO
INSERT INTO dbo.CaSy VALUES(N'Thái Thanh','thanh@thaithanh.com')
INSERT INTO dbo.CaSy VALUES(N'Khánh Ly',NULL)
INSERT INTO dbo.CaSy VALUES(N'Trần Thái Hòa',NULL)

Lệnh tạo bảng và hai lệnh INSERT đầu diễn ra bình thường, nhưng lệnh INSERT thứ ba thì gây ra lỗi sau:

Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UC_CaSy'. Cannot insert duplicate key in object 'CaSy'.
The statement has been terminated.

Như vậy là SQL Server từ chối thêm một giá trị NULL thứ hai vào cột “Email” vì nó có ràng buộc duy nhất. Điều này thật khó thuyết phục, vì NULL không phải là một giá trị để có thể so sánh được (ví dụ mệnh đề WHERE colname = @Val khi @Val là NULL sẽ không trả về bản ghi nào). Tôi cho rằng đây thuộc về lỗi nhiều hơn là một đặc tính của SQL Server.

Vậy để tạo một ràng buộc duy nhất với cách ứng xử giống như định nghĩa thông thường thì phải làm thế nào? Nếu bạn dùng bản 2005 trở về trước, có một vài cách có thể làm nhưng đều rườm rà:

- Dùng trigger: mỗi khi có INSERT hoặc UPDATE thì kiểm tra xem giá trị mới đã có trong bảng chưa, và chỉ cho phép thực hiện nếu chưa xuất hiện.

- Tạo một ràng buộc CHECK dbo.CountCol(Email) < 2, trong đó dbo.CountCol(@Email) là hàm UDF (bạn cần phải viết) trả về số lần xuất hiện của giá trị @Email trong cột Email.

- Cách làm “chuẩn” nhất có lẽ là tạo một indexed view từ trường Email với điều kiện WHERE Email IS NOT NULL. Tuy nhiên vẫn rườm rà, khi cần tìm kiếm trên trường Email bạn lại phải nhớ tìm trên view này, hoặc phải tạo một index cho trường Email trên bảng CaSy.

Từ bản SQL Server 2008 bạn có thể dùng tính năng filtered index để áp đặt ràng buộc này. Filtered index về bản chất là một non-clustered index nhưng cho phép chọn ra các bản ghi cần được index qua mệnh đề WHERE. Do vậy bạn có thể tạo một index như sau:

CREATE UNIQUE INDEX idx_CaSyEmail ON dbo.CaSy(Email)
WHERE Email IS NOT NULL

Lệnh trên tạo một unique index trên trường Email, nhưng chỉ các bản ghi có Email không NULL mới được index. Vì thế các Email NULL có thể được thêm vào bảng thoải mái, nhưng Email không NULL nếu bị lặp khi thêm vào sẽ bị index này chặn lại, do dó tính duy nhất được đảm bảo.

 

SQL server Sử Dụng Mệnh Đề OUTPUT Trong Các Lệnh DML

Mệnh đề OUTPUT bắt đầu được đưa ra từ SQL Server 2005 trong các lệnh DELETE, INSERT, UPDATE, để trả về các bản ghi bị ảnh hưởng hoặc lưu vào một bảng khác. Tính năng này rất hữu dụng khi bạn muốn lưu lại, chẳng hạn với lệnh DELETE, các bản ghi cần xóa sang một nơi khác để có thể tra cứu về sau.

Ví dụ bạn có bảng KhachHang chứa các khách hàng hiện tại, và KhachHangLuu chứa các thông tin cũ của khách hàng mỗi khi có sửa đổi:

CREATE TABLE dbo.KhachHang(
KhachHang_ID INT IDENTITY PRIMARY KEY,
Ten NVARCHAR(100),
Email VARCHAR(100)
)

CREATE TABLE dbo.KhachHangLuu(
KhachHang_ID INT,
Ten NVARCHAR(50),
Email VARCHAR(100)
)
GO
INSERT INTO dbo.KhachHang VALUES(N'Ý Lan','ylan@ylan.com')
INSERT INTO dbo.KhachHang VALUES(N'Tuấn Ngọc','tuanngoc@tuanngoc.com')
INSERT INTO dbo.KhachHang VALUES(N'Thái Hiền','thaihien@thaihien.com')
INSERT INTO dbo.KhachHang VALUES(N'Ngọc Hạ','ngocha@ngocha.com')

SELECT * FROM dbo.KhachHang

-- xóa KhachHang_ID = 4 (Ngọc Hạ)
DELETE dbo.KhachHang
OUTPUT DELETED.* INTO dbo.KhachHangLuu
WHERE KhachHang_ID = 4

-- cập nhật email của KhachHang_ID = 3
UPDATE dbo.KhachHang
OUTPUT DELETED.* INTO dbo.KhachHangLuu
SET Email = N'me@thaihien.com'
WHERE KhachHang_ID = 3

SELECT * FROM dbo.KhachHangLuu
Như vậy là bạn luôn có bản lưu chứa thông tin cũ của khách hàng trên bảng KhachHangLuu mỗi khi có sửa đổi trên bảng chính. Với SQL Server 2000 bạn phải thực hiện điều trên bằng hai lệnh, INSERT vào bảng lưu rồi thực hiện DELETE/UPDATE trên bảng chính, và để đảm bảo tính toàn vẹn bạn phải đưa hai lệnh vào một transaction. Với mệnh đề OUTPUT chỉ có một lệnh được thực hiện, và bản thân nó là một transaction nên bạn khỏi cần bận tâm về điều đó.

Một ứng dụng khác của mệnh đề OUTPUT là bạn có thể dùng nó để lấy về giá trị IDENTITY vừa được INSERT vào bảng:
INSERT INTO dbo.KhachHang
OUTPUT INSERTED.KhachHang_ID
VALUES(N'Elvis Phương','phuong@ElvisPhuong.com')

KhachHang_ID
------------
5
Trong bài Về Cột IDENTITY có một chi tiết bàn về phương pháp lấy về giá trị IDENTITY vừa được tạo, và dùng mệnh đề OUTPUT như trên đây bổ sung thêm một cách làm rất an toàn cho việc này.


Phiên bản áp dụng: SQL Server 2005 trở lên

Về Cột IDENTITY

1. Cột IDENTITY dùng để tạo một dãy số liên tục tăng cho mỗi bản ghi được INSERT, nó rất thích hợp khi bạn cần có một cột mà mỗi lần INSERT giá trị của nó được tự động tăng lên cao hơn các giá trị trước đó, trong khi bạn không quan tâm nhiều lắm đến bản thân các giá trị trong cột này. Trong rất nhiều trường hợp, cột IDENTITY cũng được dùng luôn làm khóa chính cho bảng.

Bạn khai báo thuộc tính IDENTITY cho cột như sau:

--Tạo bảng
CREATE TABLE dbo.Tblxxx(
ID_Col INT IDENTITY(1,1),
Col1 INT,
Col2 VARCHAR(50)...
)
--Thêm cột vào bảng
ALTER TABLE dbo.Tblyyy ADD ID_Col INT IDENTITY(1,1)

2. Cú pháp tổng quát của IDENTITY là:

IDENTITY(seed, increment)

 

trong đó seed là giá trị khởi tạo và increment là giá trị tăng cho mỗi lần. Khi không chỉ định seed và increment thì các giá trị mặc định của chúng được dùng là seed = 1 và increment = 1.

Ví dụ:

--giá trị khởi đầu = 1 và mỗi lần tăng 1
CREATE TABLE dbo.Tblxxx(
ID_Col INT IDENTITY...
)

--giá trị khởi đầu = 1000 và mỗi lần tăng 5
CREATE TABLE dbo.Tblxxx(
ID_Col INT IDENTITY(1000,5)...
)

-- giá trị khởi đầu = -100 và mỗi lần giảm 2
CREATE TABLE dbo.Tblxxx(
ID_Col INT IDENTITY(-100,-2)...
)

Lưu ý là chuỗi giá trị có thể giảm dần (increment < 0) như ở ví dụ trên, nhưng tôi thấy điều này ít có giá trị thực tế và thậm chí còn dễ gây nhầm lẫn. Do đó trong bài này tôi luôn giả định là chuỗi luôn tăng.

3. Bạn không thể thay đổi một cột có sẵn để nó trở thành cột IDENTITY, hoặc bỏ thuộc tính IDENTITY của nó. Khi bạn edit bảng trong Management Studio và sửa một cột thành IDENTITY hoặc ngược lại, ở phía sau thực ra SQL Server xóa bảng và tạo một bảng mới.

4. SQL Server không duy trì tính liên tục của dãy số IDENTITY, khi bạn DELETE bản ghi thì khoảng trống sẽ xuất hiện. Ở lần INSERT sau đó một giá trị mới sẽ được dùng, thay vì giá trị bị xóa được dùng lại.

5. Bản thân thuộc tính IDENTITY không đảm bảo các giá trị trong cột là duy nhất. Ví dụ, bạn có đặt lại seed với một giá trị tùy ý (kể cả giá trị đã được dùng trước đó). Khi cần đảm bảo tính duy nhất, bạn cần thêm ràng buộc khóa chính hoặc khóa duy nhất cho cột.

6. Để khởi tạo lại giá trị seed, bạn dùng lệnh DBCC CHECKIDENT

7. Khi cần INSERT vào bảng, bạn có thể bỏ qua cột IDENTITY trong danh sách các cột, và giá trị của cột IDENTITY sẽ được tự động sinh ra (đó là mục đích chính khi tạo cột này)

CREATE TABLE dbo.MyTable(
ID_Col INT IDENTITY,
Col_1 INT,
Col_2 VARCHAR(50)
)
GO
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(15,'abc')
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(62,'def')
INSERT INTO dbo.MyTable(Col_1,Col_2) VALUES(37,'ghi')

Khi bạn muốn ghi đè lên giá trị tự sinh của cột IDENTITY, bạn phải đưa cột này vào danh sách các cột đồng thời trước đó phải đặt lại lựa chọn IDENTITY_INSERT là ON (mặc định là OFF)

SET IDENTITY_INSERT dbo.MyTable ON
INSERT INTO dbo.MyTable(ID_Col,Col_1,Col_2) VALUES(100,42,'jkl')

Một tình huống thường xảy ra là bạn INSERT vào bảng từ một bảng khác có cùng cấu trúc. Một cách lười biếng bạn có thể viết thế này:

INSERT INTO dbo.Table1 SELECT * FROM dbo.Table2

Khi bảng dbo.Table1 không có cột IDENTITY, câu lệnh trên thực hiện không có gì trở ngại. Nhưng nếu bảng dbo.Table1 có chứa cột IDENTITY, bạn sẽ nhận được thông báo lỗi sau:

An explicit value for the identity column in table 'dbo.Table1' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Như vậy theo nội dung của thông báo lỗi, để thực hiện được câu lệnh INSERT trên bạn cần thực hiện hai việc: đặt IDENTITY_INSERT là ON cho bảng dbo.Table1, và liệt kê ra các cột:

 

SET IDENTITY_INSERT dbo.Table1 ON
INSERT INTO dbo.Table1(ID_Col, Col_1,...)
SELECT ID_Col, Col_1,...
FROM dbo.Table2

8. Một bảng chỉ được phép có tối đa một cột IDENTITY. Bạn có thể tìm ra cột nào là IDENTITY bằng cách edit bảng trong Management Studio, hoặc bằng cách truy vấn view hệ thống SYS.IDENTITY_COLUMNS:

SELECT *
FROM SYS.IDENTITY_COLUMNS
WHERE OBJECT_ID = OBJECT_ID('dbo.Tblxxx')

9. Để lấy giá trị IDENTITY cuối cùng vừa được INSERT, bạn dùng hàm SCOPE_IDENTITY(), gọi hàm này ở ngay dưới câu lệnh INSERT.

INSERT INTO dbo.Tblxxx(Col_1,Col_2) VALUES(15,'abc')
SELECT SCOPE_IDENTITY()

 

Ngoài ra còn một vài hàm khác có cùng mục đích, nhưng khi đi vào chi tiết chúng có những khác biệt đáng kể. Trong phần lớn trường hợp, SCOPE_IDENTITY() là hàm an toàn nhất tránh được những rắc rối không cần thiết. Bạn có thể xem một bài so sánh về các hàm này tại đây.

10. Với các tính năng như trên, cột IDENTITY sẽ gây ra khó khăn trong một số trường hợp. Nếu bạn muốn duy trì tính liên tục của dãy số (ví dụ, dùng lại các giá trị bị xóa trước đó), bạn phải tự thực hiện việc kiểm tra này trước mỗi lần INSERT. Việc xử lý cũng phức tạp hơn khi bảng nằm trong một replication với nhiều publisher cùng update dữ liệu về một subscriber. Đặc biệt với replication hai chiều (hai database update qua lại cho nhau) thì nó hoàn toàn bó tay. Lý do là vì giá trị IDENTITY kế tiếp chỉ được lưu cục bộ ở mỗi database, bảng này không biết giá trị kế tiếp của bảng ở bên database kia là bao nhiêu, cho nên khi replication xảy ra thì các giá trị INSERT vào mỗi bảng sẽ bị xung đột với nhau.

Trên Diễn đàn tin học có một thảo luận chi tiết hơn về các hạn chế của IDENTITY và các phương pháp thay thế (cũng như ưu/nhược điểm của các phương pháp này), tại đây.

 

Đặt Lại Giá Trị Của Trường IDENTITY

Khi bạn TRUNCATE bảng, trường IDENTITY sẽ được reset trở về giá trị ban đầu (khi tạo bảng). Tuy nhiên có những trường hợp bạn không thể dùng TRUNCATE (xem thêm bài DELETE và TRUNCATE). Ví dụ bạn muốn đặt lại giá trị của trường IDENTITY trong bảng dbo.TableName về 1, bạn có thể dùng lệnh này:

DBCC CHECKIDENT ('dbo.TableName', RESEED, 1)

SQL server Lưu Ý Khi Viết Câu Lệnh Dùng NOT IN

Khi bạn viết câu lệnh dùng “NOT IN”, bạn có thể sẽ gặp những tình huống không mong đợi, nếu dữ liệu có chứa giá trị NULL.

Ví dụ:

CREATE TABLE dbo.DienThoai(ID INT PRIMARY KEY, NhaSX NVARCHAR(50), 
Model VARCHAR(50), TenCSH NVARCHAR(80) )
GO
INSERT INTO dbo.DienThoai
SELECT 1, 'Apple', 'Iphone 4', N'Hồ Ngọc Hà' UNION ALL
SELECT 2, 'Vertu', 'Signature', N'Quang Dũng' UNION ALL
SELECT 3, 'Samsung','S9402 Ego', N'Mỹ Tâm' UNION ALL
SELECT 4, 'HTC',NULL, N'Mỹ Tâm' -- không có model

 

Bạn có một CSDL theo dõi các loại điện thoại di động sở hữu bởi các celebrity (biết đâu ý tưởng này lại kiếm ra tiền, hehe). Giả sử bạn lưu trữ nhà sản xuất (NhaSX), model, và tên của chủ sở hữu (TenCSH); model có thể không được biết (như bản ghi #4 ở trên). Trước khi có thể kiếm được tiền, bạn cần giải quyết mấy tình huống sau:

Tình huống 1. Tìm ra các chủ nhân có model không phải là “Iphone 4″ và “Signature”, bạn viết câu lệnh thế này:

SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN ('Iphone 4','Signature')

ID TenCSH
---------------
3 S9402 Ego

Bạn hy vọng câu lệnh trả về các bản ghi #3 và #4, nhưng thực tế nó chỉ trả về #3. Lý do là vì bản ghi #4 có Model = NULL, làm cho hệ thống không xác định được chân lý của biểu thức logic "Model NOT IN ('Iphone 4','Signature' )". Nói cách khác nó không đánh giá được biểu thức đúng hay sai; trong khi đó yêu cầu của câu lệnh là trả về bản ghi nếu biểu thức đúng.

Để khắc phục lỗi trên bạn có thể thêm "OR Model IS NULL" vào mệnh đề WHERE của câu lệnh, hoặc dùng hàm ISNULL với Model để gán cho nó một giá trị không NULL:

SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN ('Iphone 4','Signature') OR Model IS NULL

-- hoặc
SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE ISNULL(Model,'-') NOT IN ('Iphone 4','Signature')

Tình huống 2. Tìm ra các chủ nhân có model không nằm trong số các model sở hữu bởi Mỹ Tâm:

SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN (SELECT Model FROM dbo.DienThoai
                          WHERE TenCSH = N'Mỹ Tâm')

ID TenCSH
------------------
(0 row(s) affected)

Theo suy luận thông thường, bạn mong đợi câu lệnh trả về các bản ghi #1 và #2. Tuy nhiên hoàn toàn ngược lại nó không trả về bản ghi nào. Vì sao vậy? Câu lệnh con ở trên trả về hai model là ”S9402 Ego” và NULL, do đó câu lệnh chính tương đương với:

SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN ('S9402 Ego',NULL)
Áp dụng luật DeMorgan ta có thể biến đổi biểu thức ở mệnh đề WHERE như sau:
Model NOT IN ('S9402 Ego',NULL)
tương đương với:
NOT [Model IN ('S9402 Ego',NULL)]
tương đương với:
NOT [(Model = 'S9402 Ego') OR (Model = NULL)]
tương đương với:
(Model != 'S9402 Ego') AND (Model != NULL)
Biểu thức cuối cùng ở trên muốn đúng đòi hỏi cả hai biểu thức con phải đúng, trong khi (Model != NULL) thì không kết luận được. Vì thế mà cả biểu thức trên luôn luôn không xác định được (và do đó không thể gọi là đúng).

Để khắc phục bạn cũng có thể làm theo hai cách, thêm điều kiện “AND Model IS NOT NULL” vào mệnh đề WHERE của câu lệnh con, hoặc viết lại cả câu lệnh dùng “NOT EXISTS”:
SELECT ID, TenCSH
FROM dbo.DienThoai
WHERE Model NOT IN (SELECT Model FROM dbo.DienThoai
                    WHERE TenCSH = N'Mỹ Tâm' AND Model IS NOT NULL)
--hoặc
SELECT ID, TenCSH
FROM dbo.DienThoai D1
WHERE NOT EXISTS(SELECT 1 FROM dbo.DienThoai D2
                 WHERE D2.TenCSH = N'Mỹ Tâm'
                 AND D2.Model = D1.Model)