Thêm Dữ Liệu Mới Vào Bảng Dùng LEFT JOIN

Giả sử bạn có hai bảng TableA và TableB có cùng cấu trúc gồm 3 cột như sau: ID int primary key, Ten nvarchar(100), Diachi nvarchar(200); Giờ bạn muốn INSERT dữ liệu vào bảng TableA từ bảng TableB cho các dòng vốn chưa có trong bảng TableA. Bạn có thể làm thế này:

INSERT dbo.TableA(ID, Ten, Diachi)
SELECT b.ID, b.Ten, b.Diachi
FROM TableB b
LEFT JOIN TableA a ON a.ID = b.ID
WHERE a.ID IS NULL
Giải thích: Câu lệnh SELECT dùng LEFT JOIN từ TableB tới TableA với điều kiện WHERE a.ID IS NULL sẽ lọc ra các bản ghi trong TableB mà không có trong TableA. Kết quả của lệnh SELECT này được dùng để INSERT vào TableA. Vì SQL Server thực hiện câu lệnh theo tập, nên lệnh SELECT chỉ được thực hiện 1 lần và các bản ghi tìm được sẽ được dùng cho INSERT (không phải là SELECT được 1 bản ghi rồi INSERT, rồi quay lại SELECT tiếp bản ghi thứ hai rồi lại INSERT…).

Kiểm Tra Bản Ghi Tồn Tại Với IF EXISTS trong MSSQL

Trong một thủ tục, bạn muốn kiểm tra xem có bản ghi nào thỏa mãn một số điều kiện nhất định trong bảng hay không, và rẽ nhánh chương trình tùy theo kết quả kiểm tra. Bạn hãy làm thế này:

IF EXISTS(SELECT 1 FROM TblName WHERE ...)
BEGIN
...
END
ELSE
BEGIN
...
END
Đây là cách làm tối ưu, vì đoạn lệnh “EXISTS(SELECT 1 FROM TblName WHERE …)” sẽ dừng lại ngay sau khi tìm được bản ghi đầu tiên; “SELECT 1″ cũng tránh cho hệ thống khỏi phải đọc dữ liệu từ bảng. Một cách khác có thể làm là:
SELECT TOP 1 1 FROM TblName WHERE...
IF @@ROWCOUNT > 0
BEGIN
...
END
ELSE
BEGIN
...
END
Tuy nhiên các cách sau đây thì tuyệt đối nên tránh:

Cách 1: KHÔNG NÊN DÙNG

IF (SELECT COUNT(*) FROM TblName WHERE...) > 0
BEGIN
...
END
ELSE
BEGIN
...
END

 Cách 2: tệ hơn nữa, KHÔNG NÊN DÙNG

SELECT * FROM TblName WHERE...
IF @@ROWCOUNT > 0
BEGIN
...
END
ELSE
BEGIN
...
END
Lý do: Cả hai cách đều thực hiện các thao tác vượt quá cần thiết, chỉ cần bản ghi đầu tiên tìm được là đủ để chương trình đi tiếp được rồi. Việc đếm số bản ghi (cách 1) hoặc trả lại tất cả các bản ghi (cách 2) làm kéo dài thời gian thực hiện đoạn lệnh. Trong nhiều trường hợp, để tìm ra bản ghi đầu tiên có thể thực hiện rất nhanh, nhưng để tìm ra toàn bộ các bản ghi lại mất một thời gian gấp nhiều lần.

Các Kiểu Backup Trong SQL Server

Khái niệm backup (sao lưu) và restore (khôi phục) chắc hẳn đã quen thuộc đối với đa số chúng ta: bạn thường xuyên backup, ví dụ copy toàn bộ thư mục sang một thiết bị lưu trữ khác, để phòng khi gặp sự cố gây mất mát dữ liệu thì có thể copy ngược trở lại. Với database thì việc backup diễn ra có khác, khi hệ thống đang vận hành thì bạn không thể đơn giản copy các data file và log file vì chúng bị khóa hoàn toàn. Bạn phải dựa vào cơ chế backup của hệ QTCSDL. SQL Server cung cấp ba loại backup như sau:

· Full backup: backup toàn bộ dữ liệu tại thời điểm đó. Đây có lẽ là loại được dùng thường xuyên nhất.
· Differential backup: backup các trang dữ liệu mới được cập nhật kể từ lần full backup trước đó.
· Transaction log backup: backup các log record hiện có trong log file, nghĩa là nó sao lưu các hành động (các thao tác xảy ra đối với database) chứ không sao lưu dữ liệu. Đồng thời nó cũng cắt bỏ (truncate) log file, loại bỏ các log record vừa được backup ra khỏi log file. Vì thế khi thấy log file tăng quá lớn, có nhiều khả năng là bạn chưa từng backup transaction log bao giờ.

Một nguyên tắc chung để giảm bớt lượng dữ liệu mất mát khi có sự cố là tăng tần suất backup. Tuy nhiên với một database có dung lượng lớn và được cập nhật liên tục, thì việc thực hiện full backup với tần suất cao là không khả thi, vì nó dùng rất nhiều CPU và I/O. Nhờ có differential backup và transaction log backup, bạn có thể tạo lập các phương án sao lưu thích hợp, đảm bảo dữ liệu được backup thường xuyên hơn mà không chiếm nhiều tài nguyên của hệ thống. Ví dụ, bạn có thể thực hiện:

· Full backup: một lần mỗi ngày vào 2h sáng.

· Differential backup: vào các thời điểm 6h, 10h, 14h, 18h, 22h (5 lần/ngày).

· Transaction log backup: 15 phút một lần vào các thời điểm 5′, 20′, 35′, và 50′ của mỗi giờ (4 lần/giờ). Chọn thời điểm như vậy để đảm bảo nó xảy ra sau differential backup.

Lưu ý là differential backup luôn sao lưu các trang đã thay đổi kể từ lần full backup trước (trong ví dụ trên là các trang đã thay đổi kể từ 2h), chứ không phải từ lần differential backup trước đó. Vì thế bản backup lúc 10h sẽ bao gồm các trang lưu trong bản lúc 6h, bản 14h gồm các trang đã có trong bản 10h… Transaction log backup thì ngược lại, chỉ sao lưu các log record kể từ lần transaction log backup trước đó.

Giả sử database bị hỏng vào thời điểm 10h55′, bạn cần khôi phục lại database theo trình tự sau:

Bước 1. Khôi phục từ bản full backup gần với thời điểm có sự cố nhất (bản full backup lúc 2h).

Bước 2. Khôi phục từ bản differential backup gần với thời điểm có sự cố nhất (bản lúc 10h).

Bước 3. Khôi phục tất cả các transaction log backup kể từ sau lần diferential backup gần đây nhất, lần lượt theo trình tự thời gian. Đó là các bản tại các thời điểm 10h5′, 1oh20′, 10h35′, và 10h50′.

Bước 1 và 2 đưa database trở lại trạng thái giống như lúc 10h. Ở bước 3, với mỗi lần khôi phục transaction log thì các thao tác chứa trong đó được đem ra thực hiện lại trên database (gọi là log forwarding) và do đó đưa nó về trạng thái gần hơn thời điểm xảy ra sự cố. Như vậy sau khi hoàn tất khôi phục bốn bản transaction log backup thì database sẽ ở vào trạng thái giống như lúc 10h50′. Tuy nhiên các thay đổi diễn ra trong 5 phút sau đó (từ 10h50′ đến 10h55′) đã vĩnh viễn bị mất.

Trong trường hợp may mắn hơn, khi sự cố xảy ra mà log file vẫn còn nguyên vẹn, bạn sẽ có cơ hội đưa database trở lại trạng thái ngay trước khi có sự cố, và do đó không có mất mát dữ liệu. Việc đầu tiên bạn cần làm là thực hiện ngay transaction log backup (nên nhớ, không được vội vàng khôi phục từ bản full backup). Sau đó các bước tiếp theo sẽ tương tự như trên:

Bước 0.
Sao lưu transaction log.

Bước 1. Khôi phục từ full backup file của sáng sớm hôm đó.

Bước 2. Khôi phục từ differential backup file lúc 10h.

Bước 3. Khôi phục các transaction log backup file kể từ sau 10h, lần lượt theo trình tự thời gian: các bản backup vào lúc 10h5′, 1oh20′, 10h35′, 10h50′, và cuối cùng bản lúc 10h55′ (vừa thực hiện ở bước 0).

Trong bài sau tôi sẽ giới thiệu script thực hiện từng bước qua một ví dụ cụ thể.

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