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)

Index Giúp Tăng Hiệu Năng Thực Hiện Như Thế Nào

Index là phương tiện rất mạnh để tăng hiệu năng thực hiện của câu lệnh. Bài post này sẽ cung cấp một ví dụ cho bạn thấy bên trong SQL Server sử dụng index để  tăng hiệu năng như thế nào. Ở đây tôi dùng database AdventureWork là database mẫu đi kèm với SQL Server (bạn có thể dowload database này về và cài vào nếu chưa có sẵn).

Trước hết ta hãy dùng bảng Sale.Customer để tạo ra hai bảng mới là Sale.Customer_noIndex và Sale.Customer_Index, đồng thời tạo 1 index trên trường CustomerID cho bảng Sale.Customer_Index:

SELECT *
INTO Sales.Customer_NoIndex
FROM Sales.Customer

SELECT *
INTO Sales.Customer_Index
FROM Sales.Customer
GO
CREATE INDEX Idx_Customer_Index_CustomerID ON Sales.Customer_Index(CustomerID)
Nay ta có hai câu lệnh SELECT sau để truy vấn hai bảng:

-- #1
SELECT CustomerID, CustomerType
FROM Sales.Customer_NoIndex
WHERE CustomerID = 11001
-- #2
SELECT CustomerID, CustomerType
FROM Sales.Customer_Index
WHERE CustomerID = 11001

Hai câu lệnh này sẽ cho cùng kết quả, khác biệt duy nhất là câu lệnh thứ hai truy vấn bảng Sales.Customer_Index có index trên trường cần tìm (CustomerID). Ta sẽ xem hai câu lệnh trên được thực hiện như thế nào bằng cách nhìn vào kế hoạch thực thi (execution plan) của chúng. Khi bắt đầu thực hiện một câu lệnh, SQL Server lên một kế hoạch gồm các bước sẽ tiến hành để thực thi câu lệnh đó, gọi là kế hoạch thực thi.Trên hàng công cụ bạn hãy bấm vào nút “Include Actual Execution Plan”. Khi đó, mỗi lần bạn chạy câu lệnh hệ thống sẽ vừa thực hiện câu lệnh vừa đồng thời trả lại kế hoạch thực thi mà nó đã dùng để thực hiện câu lệnh đó.

 

Bạn hãy bôi đen câu lệnh thứ nhất và thực hiện nó, ở tab “Execution plan” hiện ra kế hoạch thực thi như thế này:

Như vậy ta thấy hệ thống sẽ thực thi câu lệnh bằng cách duyệt qua cả bảng (table scan) và tìm ra các bản ghi thỏa mãn yêu cầu tìm kiếm. Thao tác duyệt bảng có nghĩa là hệ thống cần phải đọc tuần tự từng bản ghi từ đầu đến cuối để tìm ra kết quả. Trong trường hợp này, nó phải đọc toàn bộ 19 185 bản ghi và tìm ra bản ghi có CustomerID=11011. Đây là một thao tác rất chậm vì nó phải xử lý tất cả các bản ghi trong bảng. Nên nhớ hệ thống sẽ không dừng lại khi nó tìm được bản ghi đầu tiên có CustomerID=11011, vì nó không biết liệu còn bản ghi nào khác có giá trị CustomerID tương tự hay không, cho nên để chắc chắn trả lại kết quả đầy đủ hệ thống vẫn phải tiếp tục đọc các bản ghi còn lại. Ta có thể nhận xét thấy chi phí của thao tác duyệt bảng tăng tuyến tính cùng với số lượng bản ghi trong bảng (độ phức tạp là O(n)).

Giờ ta hãy thực hiện câu lệnh thứ hai, lần này kế hoạch thực thi sẽ như sau:

Lần này ta không thấy thao tác table scan nữa, mà thay vào đó là index seek và RID lookup. Index seek là khi hệ thống có thể nhảy đến được node trên cây index chứa khóa thỏa mãn yêu cầu tìm kiếm. Index là một cấu trúc dữ liệu có dạng B-tree, nên nó rất thích hợp với các thao tác tìm kiếm theo kiểu key=value, chỉ cần vài phép so sánh là hệ thống định vị được node chứa khóa cần tìm. Node này chứa khóa (trường được index, ở đây là giá trị của CustomerID) và RID là ID của bản ghi tương ứng trong bảng (đây là giá trị nội bộ chỉ dùng bên trong hệ thống, ta không truy cập được giá trị này). Vì thế bước tiếp theo là dùng RID này để nhảy đến bản ghi tương ứng trong bảng (RID lookup) để lấy các trường dữ liệu cần thiết. Với index seek, độ phức tạp giảm xuống thành O(logn), một bước tiến vượt bậc so với table scan.

Ta có thể so sánh chi phí của hai câu lệnh trên bằng cách thực hiện cả hai cùng nhau:

Ta thấy câu lệnh thứ nhất chiếm tới 95% tổng chi phí, trong khi câu lệnh thứ hai chỉ chiếm có 5%. Nói cách khác, index trên trường CustomerID đã giúp cho câu lệnh thực hiện nhanh lên đến 19 lần. Index đã giúp cho lượng dữ liệu hệ thống cần xử lý để tìm ra kết quả giảm xuống đến mức tối thiểu, và điều đó đã tạo ra bước nhảy về tốc độ. Từ đây ta rút ra một bài học quan trọng: Các trường thường được dùng trong mệnh đề WHERE là các ứng cử viên đầu tiên cần được tạo index.

Tối Ưu Hóa Câu Lệnh Bằng Covering Index Trong SQL server

Khi một non-clustered index được dùng để thực thi một câu lệnh, ta thường thấy trong kế hoạch thực thi thao tác Key Lookup (hoặc Bookmark Lookup ở các phiên bản trước), là thao tác mà hệ thống sau khi tìm kiếm trên cây index nhảy tới bản ghi tương ứng trong bảng để lấy các trường dữ liệu cần trả về:

USE AdventureWorks
GO
SELECT ContactID, FirstName, LastName
FROM  Person.Contact
WHERE EmailAddress = 'kristina1@adventure-works.com'

Ta thấy index IX_Contact_EmailAddress trên trường EmailAddress đã được sử dụng (thao tác Index Seek), và câu lệnh đạt được hiệu năng tốt hơn rất nhiều so với quét bảng khi không có index. Tuy nhiên thao tác Key Lookup ở đó vẫn chiếm tới một nửa chi phí câu lệnh. Một kỹ thuật có thể giúp tối ưu hơn nữa cho câu lệnh này, là loại bỏ Key Lookup bằng cách đưa các trường dữ liệu cần thiết vào cây index. Khi đó hệ thống chỉ cần tìm trên index và trả kết quả về cho câu lệnh mà không cần phải truy nhập vào bảng. Index lúc đó được gọi là covering index cho câu lệnh.

Ở phiên bản SQL Server 2000 trở về trước, cách làm duy nhất để đạt được điều này là tạo index trên tất cả các cột cần tìm, như ở ví dụ trên là tạo một index phức hợp gồm ba trường EmailAddress, FirstName và LastName. Tuy nhiên cách làm này không phải luôn khả thi, ví dụ khi index trên một mình trường EmailAddress là cần thiết để duy trì ràng buộc duy nhất trên trường này; đưa thêm các trường khác vào index làm phá vỡ ràng buộc này. Hoặc khi trường cần đưa vào có kiểu VARCHAR(MAX) – kiểu dữ liệu này không thể tạo được index.

Phiên bản SQL Server 2005 bắt đầu bổ sung thêm lựa chọn INCLUDE trong lệnh CREATE INDEX để tăng khả năng covering của index:

CREATE NONCLUSTERED INDEX IX_Contact_EmailAddress
ON Person.Contact(EmailAddress)
INCLUDE (FirstName,LastName)
WITH (DROP_EXISTING  = ON) -- xóa index nếu đã được tạo trước đó

Lệnh trên vẫn tạo index trên trường EmailAddress, nhưng đồng thời “ký gửi” hai trường FirstName và LastName vào đó. Cần lưu ý là hai trường này không thuộc về khóa index. Ví dụ nếu đây là unique index thì trường EmailAddress vẫn phải duy nhất. Khi thực hiện lại câu lệnh SELECT ở trên, kế hoạch thực thi đã thay đổi:

 

Như vậy Key Lookup đã biến mất, tức là bước truy nhập vào bảng đã bị loại bỏ, vì tất cả các trường dữ liệu mà câu lệnh yêu cầu đã được tìm thấy ngay tại index. Điều này cũng có nghĩa là chi phí câu lệnh được giảm đi một nửa. Thực tế đây là mức tối ưu nhất mà một câu lệnh có thể đạt được khi dùng non-clustered index. Một ưu điểm nữa khi chỉ cần đọc index là nó làm giảm tranh chấp trên bảng với các câu lệnh khác đang đồng thời truy nhập vào bảng đó.

Cũng từ kế hoạch thực thi trên ta có thêm một nhận xét là trường ContactID, vốn là khóa chính trong bảng, cũng được đọc từ cây index mà không cần quay sang bảng. Sở dĩ như vậy là vì, trường khóa chính luôn được lưu trên cây index để dùng làm con trỏ đến bản ghi tương ứng trong bảng (khi bảng không có khóa chính thì một giá trị định danh bản ghi, RowID, được dùng).

Lưu ý là kỹ thuật trên cũng làm tăng chi phí cho việc UPDATE và INSERT dữ liệu. Ví dụ khi bạn cập nhật FirstName và LastName, không những bản ghi trong bảng mà cả node index tương ứng cũng cần được cập nhật theo. Bạn cần thử nghiệm kỹ càng xem hiệu năng của cả hệ thống có bị ảnh hưởng không trước khi áp dụng kỹ thuật này.


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