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.