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.
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:
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ả:
Để 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:
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.
Để đị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.
Bạn có thể vui lòng tắt trình chặn quảng cáo ❤️ để hỗ trợ chúng tôi duy trì hoạt động của trang web.
Trong hướng dẫn này, bạn sẽ tìm hiểu về GUID trong SQL Server và cách sử dụng hàm NEWID() để tạo giá trị GUID.
Trong hướng dẫn này, bạn sẽ học cách sử dụng kiểu dữ liệu DATETIMEOFFSET trong SQL Server để thao tác datetime với múi giờ.
Trong hướng dẫn này, bạn sẽ học cách lưu trữ thời gian trong ngày trong cơ sở dữ liệu bằng cách sử dụng kiểu dữ liệu TIME trong SQL Server.
Trong hướng dẫn này, bạn sẽ học cách sử dụng kiểu dữ liệu DATE trong SQL Server để lưu trữ dữ liệu ngày tháng trong một bảng.