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

Posted on by adminChuyên mục:Kiến thức Internet

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

  • Trung Nguyen

  • 27/11/2021

  • 5 min read

Trong hướng dẫn này, bạn sẽ học cách sử dụng cột được đo lường và thống kê ( computed column ) của SQL Server để sử dụng lại logic giám sát 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 ứng cho tất cả chúng ta một tính năng gọi là cột được giám sát được 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 những 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:

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

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

Các cột được đo lường và thống kê hoàn toàn có thể tàng trữ. Nó có nghĩa là SQL Server tàng trữ vật lý tài liệu của những cột được giám sát trên đĩa .Khi bạn biến hóa tài liệu trong bảng, SQL Server sẽ đo lường và thống kê hiệu quả dựa trên biểu thức của những cột được thống kê giám sát và tàng trữ về mặt vật lý những tác dụng trong những cột sống sót này .Khi bạn truy vấn tài liệu từ những cột được đo lường và thống kê, SQL Server chỉ cần truy xuất tài liệu mà không cần triển khai bất kể phép tính nào. Điều này giúp tránh ngân sách giám sát với ngân sách tàng trữ bổ trợ .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 thời điểm ngày hôm nay :

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

Chúng ta hoàn toàn có thể sử dụng biểu thức này để xác lập 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ả :Lưu trữ dữ liệu cột được tính toán trong SQL Server

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 thống kê giám sát trong SQL Server để sử dụng lại logic giám sát trong nhiều truy vấn .

Nếu Comdy hữu ích và giúp bạn tiết kiệm thời gian làm việc

Bạn hoàn toàn có thể sung sướng đưa Comdy vào whitelist của trình chặn quảng cáo ❤ ️ để tương hỗ chúng tôi trong việc trả tiền cho dịch vụ tàng trữ web để duy trì hoạt động giải trí của website .

admin