Cột được tính toán trong SQL Server

Trong hướng dẫn này, bạn sẽ học cách sử dụng cột được tính toán (computed column) của SQL Server để sử dụng lại logic tính toán trong nhiều truy vấn.

Giới thiệu về cột được tính toán trong SQL Server

Hãy tạo một bảng mới có tên persons để minh họa:

CREATE TABLE persons
(
    person_id  INT PRIMARY KEY IDENTITY, 
    first_name NVARCHAR(100) NOT NULL, 
    last_name  NVARCHAR(100) NOT NULL, 
    dob        DATE
);

Và insert hai dòng dữ liệu mẫu vào bảng persons:

INSERT INTO 
    persons(first_name, last_name, dob)
VALUES
    ('John','Doe','1990-05-01'),
    ('Jane','Doe','1995-03-01');

Để truy vấn tên đầy đủ của những người trong bảng persons, bạn thường sử dụng hàm CONCAT() hoặc toán tử + như sau:

SELECT
    person_id,
    first_name + ' ' + last_name AS full_name,
    dob
FROM
    persons
ORDER BY
    full_name;

Việc thêm biểu thức first_name + ' ' + last_name AS full_name trong mọi truy vấn không thuận tiện và khó bảo trì.

May mắn thay, SQL Server cung cấp cho chúng ta một tính năng gọi là cột được tính toán cho phép bạn thêm một cột mới vào bảng với giá trị được lấy từ giá trị của các cột khác trong cùng một bảng.

Ví dụ: bạn có thể thêm cột full_name vào bảng persons bằng cách sử dụng câu lệnh ALTER TABLE ADD COLUMN như sau:

ALTER TABLE persons
ADD full_name AS (first_name + ' ' + last_name);

Mỗi khi bạn truy vấn dữ liệu từ bảng persons, SQL Server sẽ tính toán giá trị cho cột full_name dựa trên biểu thức first_name + ' ' + last_name và trả về kết quả.

Đây là truy vấn mới, nhỏ gọn hơn rất nhiều:

SELECT 
    person_id, 
    full_name, 
    dob
FROM 
    persons
ORDER BY 
    full_name;

Nếu bạn kiểm tra bảng persons, bạn có thể thấy cột full_name mới xuất hiện trong danh sách cột:

Lưu trữ dữ liệu cột được tính toán trong SQL Server

Các cột được tính toán có thể lưu trữ. Nó có nghĩa là SQL Server lưu trữ vật lý dữ liệu của các cột được tính toán trên đĩa.

Khi bạn thay đổi dữ liệu trong bảng, SQL Server sẽ tính toán kết quả dựa trên biểu thức của các cột được tính toán và lưu trữ về mặt vật lý các kết quả trong các cột tồn tại này.

Khi bạn truy vấn dữ liệu từ các cột được tính toán, SQL Server chỉ cần truy xuất dữ liệu mà không cần thực hiện bất kỳ phép tính nào. Điều này giúp tránh chi phí tính toán với chi phí lưu trữ bổ sung.

Hãy xem xét ví dụ sau.

Đầu tiên, hãy xóa cột full_name của bảng persons bằng lệnh ALTER TABLE DROP COLUMN sau:

ALTER TABLE persons 
DROP COLUMN full_name; 

Sau đó, thêm cột full_name mới vào persons bảng với thuộc tính PERSISTED:

ALTER TABLE persons 
ADD full_name AS (first_name + ' ' + last_name) PERSISTED;

Lưu ý rằng một cột được tính toán chỉ tồn tại nếu biểu thức của nó là xác định. Nó có nghĩa là đối với một tập hợp các đầu vào, biểu thức luôn trả về cùng một kết quả.

Ví dụ, biểu thức first_name + ' ' + last_name là xác định. Tuy nhiên, hàm GETDATE() là một hàm không xác định vì nó trả về một giá trị khác vào một ngày khác.

Công thức này trả về tuổi tính theo năm dựa trên ngày sinh và ngày hôm nay:

(CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000

Chúng ta có thể sử dụng biểu thức này để xác định cột tính tuổi trong năm.

Câu lệnh sau cố gắng định nghĩa cột được tính toán là age_in_year được lưu trữ:

ALTER TABLE persons
ADD age_in_years 
    AS (CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000 
PERSISTED;

SQL Server sẽ báo lỗi sau:

Computed column 'age_in_years' in table 'persons' cannot be persisted because the column is non-deterministic. 

Nếu bạn xóa thuộc tính PERSISTED, nó sẽ hoạt động:

ALTER TABLE persons
ADD age_in_years 
    AS (CONVERT(INT,CONVERT(CHAR(8),GETDATE(),112))-CONVERT(CHAR(8),dob,112))/10000;

Bây giờ, bạn có thể truy vấn tuổi tính theo năm của những người trong bảng persons như sau:

SELECT 
    person_id, 
    full_name, 
    age_in_years
FROM 
    persons
ORDER BY 
    age_in_years DESC;

Đây là tập kết quả:

Cú pháp để thêm cột được tính toán vào bảng

Để thêm cột được tính mới vào bảng hiện có, bạn sử dụng cú pháp sau:

ALTER TABLE table_name 
ADD column_name AS expression [PERSISTED]; 

Trong cú pháp này:

• Đầu tiên, chỉ định tên của bảng mà bạn muốn thêm cột được tính toán vào.
• Thứ hai, chỉ định tên cột được tính toán với biểu thức trả về các giá trị cho cột.
• Cuối cùng, nếu biểu thức là xác định và bạn muốn lưu trữ dữ liệu của cột được tính toán về mặt vật lý, bạn có thể sử dụng thuộc tính PERSISTED.

Lưu ý rằng bạn có thể tạo chỉ mục trên cột được tính toán được lưu trữ để cải thiện tốc độ truy xuất dữ liệu từ cột được tính toán.

Cú pháp định nghĩa cột được tính toán khi tạo một bảng mới

Để định nghĩa cột được tính toán khi bạn tạo bảng, bạn sử dụng cú pháp sau:

CREATE TABLE table_name(
    ...,
    column_name AS expression [PERSISTED],
    ...
);

Trong hướng dẫn này, bạn đã học cách sử dụng cột được tính toán trong SQL Server để sử dụng lại logic tính toán trong nhiều truy vấn.