Вычисляемые столбцы SQL Server

В этом руководстве вы узнаете, как использовать вычисляемые столбцы SQL Server для повторного использования логики вычислений в нескольких запросах.

Введение в вычисляемые столбцы SQL Server

Давайте создадим новую таблицу под названием «Персоны» для демонстраций:

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

И вставляем две строки в таблицу персон:

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

Чтобы запросить полные имена людей в таблице persons, обычно используется функцияCONCAT() или оператор + следующим образом:

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

Добавлять выражение full_name first_name + ' ' + last_name в каждый запрос неудобно.

К счастью, SQL Server предоставляет нам функцию, называемую вычисляемыми столбцами, которая позволяет добавлять в таблицу новый столбец со значением, полученным из значений других столбцов в той же таблице.

Например, вы можете добавить столбец full_name в таблицу persons, используя столбец ALTER TABLE ADD следующим образом:

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

Каждый раз, когда вы запрашиваете данные из таблицы persons, SQL Server вычисляет значение для столбца full_name на основе выражения first_name + ' ' + last_name и возвращает результат.

Вот новый запрос, который более компактен:

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

Если вы посмотрите таблицу persons, то увидите, что в списке столбцов появился новый столбец full_name:

Сохраненные вычисляемые столбцы

Вычисляемые столбцы могут быть сохранены. Это означает, что SQL Server физически хранит данные вычисляемых столбцов на диске.

Когда вы изменяете данные в таблице, SQL Server вычисляет результат на основе выражения вычисляемых столбцов и физически сохраняет результаты в этих сохраняемых столбцах. Когда вы запрашиваете данные из сохраняемых вычисляемых столбцов, SQL Server нужно просто извлечь данные, не выполняя никаких вычислений. Это позволяет избежать накладных расходов на вычисления с расходами на дополнительное хранилище.

Рассмотрим следующий пример.

Сначала удалите столбец full_name из таблицы persons:

ALTER TABLE persons
DROP COLUMN full_name;

Затем добавьте новый столбец full_name в таблицу persons со свойством PERSISTED:

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

Обратите внимание, что вычисляемый столбец сохраняется только в том случае, если его выражение является детерминированным. Это означает, что для набора входных данных выражение всегда возвращает один и тот же результат.

Например, выражение first_name + ' ' + last_name является детерминированным. Однако функция GETDATE() является недетерминированной функцией, поскольку она возвращает другое значение в другой день.

Эта формула возвращает возраст в годах на основе даты рождения и сегодняшнего дня:

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

Мы можем использовать это выражение для определения вычисляемого столбца возраста в годах.

Следующий оператор пытается определить столбец age_in_yearcomputed как постоянный вычисляемый столбец:

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

SQL-сервер выдает следующую ошибку:

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

Если удалить свойство PERSISTED, то это должно работать:

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

Теперь вы можете запросить возраст людей в таблице лиц следующим образом:

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

Вот результат:

Синтаксис добавления вычисляемых столбцов в таблицу

Чтобы добавить новый вычисляемый столбец в существующую таблицу, используйте следующий синтаксис:

ALTER TABLE table_name
ADD column_name AS expression [PERSISTED];

В этом синтаксисе:

• Сначала укажите имя таблицы, в которую вы хотите добавить вычисляемый столбец.
• Во-вторых, укажите имя вычисляемого столбца с выражением, которое возвращает значения для столбца.
• В-третьих, если выражение детерминировано и вы хотите сохранить данные вычисляемого столбца физически, вы можете использовать свойство PERSISTED.

Обратите внимание, что вы можете создать индекс на сохраняемом вычисляемом столбце, чтобы улучшить скорость извлечения данных из вычисляемого столбца. Это хорошее альтернативное решение для индексов Oracle на основе функций или индексов на выражениях PostgreSQL.

Синтаксис определения вычисляемых столбцов при создании новой таблицы

Чтобы определить вычисляемый столбец при создании таблицы, используйте следующий синтаксис:

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

В этом руководстве вы узнали, как использовать вычисляемые столбцы SQL Server для повторного использования логики вычислений в нескольких запросах.