Tản mạn về sql

Giá trị NULL

NULL là một giá trị đặc biệt có mặt trong tất cả các loại dữ liệu, từ kiểu số, ngày, chuỗi, đến bit… Nó đại diện cho giá trị “không biết”, hoặc “không tồn tại”.

NULL không trùng với số 0 của kiểu số và cũng không trùng với chuỗi trống (') của kiểu chuỗi.

Ví dụ bạn có bảng dữ liệu users có chứa cột age; nếu một bản ghi chứa NULL ở cột này có nghĩa là ta không biết số tuổi của user đó là bao nhiêu; điều này khác với khi nó chứa 0 nghĩa là user có số tuổi bằng 0.

NULL nghĩa là “không biết” nên mọi thao tác với NULL đều dẫn đến NULL. Đây là điều bạn cần ghi nhớ để tránh phiền phức khi liên quan đến xử lý NULL.

Nếu bạn cộng một số hoặc chuỗi với NULL, kết quả sẽ là NULL.

Khi so sánh với NULL, ta cũng nhận được kết quả NULL thay vì giá trị boolean true hay false. Vì thế khi truy vấn bảng mà trong mệnh đề WHERE có dùng cột chứa NULL, các bản ghi NULL luôn là ngoại lệ. Để lấy được các bản ghi này bạn cần dùng “IS NULL”. Còn để lấy được các bản ghi không NULL, bạn dùng “IS NOT NULL”.

Để chuyển đổi NULL về một giá trị chỉ định, bạn dùng hàm ISNULL().

Khi bạn dùng câu lệnh "NOT IN", sẽ có tình huống không mong đợi xảy ra với dữ liệu chứa giá trị là NULL.

Ví dụ: tạo bảng dữ liệu sau

CREATE TABLE users(ID INT PRIMARY KEY, user_name NVARCHAR(50), age int, email VARCHAR(80) )
GO
INSERT INTO users
SELECT 1, 'nguyen van a', 13, 'a@gmail.com' UNION ALL
SELECT 2, 'nguyen van b', 15, 'b@gmail.com' UNION ALL
SELECT 3, 'le thi c',12, 'c@gmail.com' UNION ALL
SELECT 4, 'tran hong d',NULL, 'd@gmail.com' -- age = null

tình huống : tìm ra user có tuổi không phải 13 và 15

SELECT * FROM users WHERE age NOT IN ('13','15');

Kết quả mong đợi: có hai bản ghi là bản ghi 3 và 4.

Thực tế: kết quả trả về một bản ghi là bản ghi 3. Vì bản ghi 4 có giá trị age= null nên hệ thống không xác định được age NOT IN ('13','15') là true or false. Nó không đánh giá được kết quả là true hay false, trong khi đó yêu cầu của biểu thức là trả về kết quả true.

**Cách khắc phục: **

SELECT * FROM users WHERE age NOT IN ('13','15') OR age IS NULL;

hoặc

SELECT * FROM users WHERE ISNULL(age,'-') NOT IN ('13','15') OR age IS NULL;
-- gán cho age một giá trị khác null

Khi cột được khai báo là khóa chính, nó phải là NOT NULL, vì nếu chứa NULL nó không còn khả năng làm định danh cho bản ghi nữa.

Khi cột chứa NULL được tạo index, tất cả các bản ghi chứa NULL được lưu chung vào một node index, chúng được coi như có cùng giá trị.

Vì thế nếu index là unique index, chỉ tối đa một bản ghi được chứa NULL.

Nếu một cột làm khóa ngoại và tham chiếu đến một cột ở bảng khác, cột khóa ngoại đó có thể chứa NULL mà không bị coi là vi phạm ràng buộc.

Vì NULL có mặt trong mọi kiểu dữ liệu, bạn có thể CAST NULL (CAST là hàm chuyển đổi kiểu dữ liệu) đến bất kỳ kiểu dữ liệu nào bạn cần.

Khi dùng các hàm tính toán aggregate (như COUNT, SUM…), SQL loại bỏ các bản ghi NULL ra trước khi thực hiện do đó tránh được rắc rối khi dính với NULL.

SELECT COUNT(*), COUNT(age) FROM users

giá trị trả về:

COUNT(*) = 4;
COUNT(age) = 3;

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.

Các chế độ phục hổi của Database

Chế độ phục hồi (recovery model) là thuộc tính của database, liên quan đến cơ chế sử dụng log file và do đó, liên quan đến khả năng khôi phục dữ liệu của database. Các model đó là FULL, SIMPLE, và BULK-LOGGED.

Bạn có thể xem và thay đổi chế độ phục hồi của database bằng code như sau:

-- lấy chế độ phục hồi hiện tại của database
SELECT recovery_model_desc FROM SYS.databases WHERE name='demo'

-- thiết lập chế độ phục hồi cho database
ALTER DATABASE demo SET RECOVERY FULL
ALTER DATABASE demo SET RECOVERY SIMPLE
ALTER DATABASE demo SET RECOVERY BULK_LOGGED

Trong đó:

FULL: Lưu tất cả các transaction tuần tự trong log file và giữ chúng ở đó đến khi log file được backup. Khi log backup được thực hiện, các transaction xảy ra kể từ lần log backup trước được ghi ra backup file và loại bỏ khỏi log file. Chính xác ra là chúng được đánh dấu là đã được backup và có thể được ghi đè bởi các transaction đến sau. Nếu là lần log backup đầu tiên kể từ khi database được tạo thì nó gồm tất cả transaction từ lần full backup. Vì thế bạn chỉ có thể backup log nếu recovery model là FULL và database đã từng được full backup.

Ở chế độ FULL, các transaction được lưu nguyên vẹn và thành chuỗi liên tục. Khi được sao lưu log backup định kỳ, database có thể được khôi phục tới một thời điểm bất kỳ, và do đó hạn chế tối đa mất mát dữ liệu khi có sự cố xảy ra. Đây là chế độ cần thiết cho các OLTP database (OLTP: Online transaction processing), và cũng là chế độ mặc định mỗi khi database mới được tạo.

Cũng vì các transaction được lưu nguyên vẹn đến khi được log backup, nếu bạn không thực hiện log backup định kỳ kích thước của log file sẽ không ngừng tăng lên. Khi log file đầy, SQL Server sẽ xin cấp phát thêm không gian đĩa để log file có thể chứa thêm transaction. Nếu bạn đặt kích thước tối đa cho log file và đã tăng tới kích thước này, hoặc đĩa hết chỗ trống, SQL Server sẽ báo lỗi và hủy bỏ transaction.

Có thể so sánh chế độ FULL với quyển sổ ghi nhật ký hàng ngày của bạn. Giả sử bạn muốn giữ quyển sổ nhật ký luôn có độ dày 100 trang. Cứ mỗi tuần bạn lại sao lưu các trang đã viết bằng cách xé chúng khỏi sổ và cất vào tủ, đồng thời thêm ngần đấy trang trắng vào thay thế. Các trang trắng này lại được dùng để viết nhật ký cho các ngày tiếp. Nếu không sao lưu, bạn không thể thêm trang trắng vào sổ và chằng bao lâu cuốn sổ sẽ đầy không còn chỗ để viết tiếp. Muốn viết tiếp bạn phải tăng số trang của quyển sổ lên hơn 100 và thêm các trang trắng vào cuối.

SIMPLE: Chế độ SIMPLE không quan tâm đến chuyện log đã được backup hay chưa. Mỗi khi transaction kết thúc, nó sẽ được đánh dấu là cho phép ghi đè lên và các transaction tiếp theo sẽ dùng lại không gian đó. Vì thế log backup không thể thực hiện ở chế độ này và kích thước log file cũng không bị tăng liên tục. Tuy nhiên trong nội bộ 1 transaction, các cập nhật vẫn được lưu vào log file đầy đủ, và log file vẫn cần đủ lớn để chứa các cập nhật này. Vì thế kích thước log file tương đương với kích thước của transaction lớn nhất đã từng được thực hiện trong database.

Việc so sánh tương đồng với sổ nhật ký, chế độ SIMPLE giống như khi viết đến trang cuối thì bạn quay lại trang đầu, tẩy hết những gì đã viết và viết đè lên đó.

Vì không thể thực hiện được log backup, chế độ SIMPLE không cho phép khả năng khôi phục dữ liệu đến một thời điểm nhất định. Bạn chỉ có thể khôi phục lại dữ liệu từ bản full backup (hoặc differential backup) gần đây nhất. Do đó chế độ này không thích hợp với các hệ thống OLTP. Tuy nhiên do ưu điểm giảm nhẹ gánh nặng về quản lý log file bạn có thể đặt chế độ này cho các database ở môi trường development. Bạn cũng có thể chọn đặt chế độ này cho các database phục vụ báo cáo, database cho các ứng dụng DW/BI.

BULK-LOGGED: Chế độ này hoạt động giống như chế độ FULL, tuy nhiên nó chỉ log tối thiểu các thao tác bulk (liên quan đến nhiều bản ghi) như bcp, BULK INSERT, hay SELECT INTO. Điều này giúp cho các thao tác trên thực hiện nhanh hơn, vì không phải log chi tiết từng thay đổi như với chế độ FULL. Giống như FULL, chế độ này vẫn đòi hỏi phải backup log định kỳ nếu không kích thước log file sẽ tăng. Nhưng nó lại mất khả năng khôi phục tới một thời điểm nhất định như với chế độ FULL. Bản thân tôi không nhìn thấy nhiều giá trị của chế độ BULK-LOGGED.