В базах данных часто возникают ситуации, когда значения должны быть вычислены или столбцы должны быть обновлены на основе других столбцов. Традиционно для синхронизации этих значений использовались триггеры, но они могут вносить накладные расходы и сложность. В этой статье мы рассмотрим более эффективный и стандартный подход к обработке таких сценариев с использованием вычисляемых столбцов в SQL Server.
Что такое вычисляемые столбцы?
Вычисляемый столбец – это столбец, вычисляемый из выражения с использованием других столбцов в той же таблице. Выражение может включать функции, переменные, константы, имена не вычисляемых столбцов и операторы. Вычисляемые столбцы предоставляют возможность вычислять значения или генерировать значения на основе манипуляции других столбцов без необходимости использования триггеров или ручных вычислений.
Реализация вычисляемых столбцов
Давайте рассмотрим пример, где у нас есть таблица с названием “CCtest” в базе данных AdventureWorks с тремя столбцами: [empNumb], [DOBirth] и [DORetirement]. Мы хотим вычислить “Дата выхода на пенсию” для каждого сотрудника как ([DOBirth] + 60 лет – 1 день). Вместо того, чтобы вычислять его каждый раз в отчете или обновлять столбец [DORetirement] через триггер при обновлении [DOBirth], мы можем создать [DORetirement] как вычисляемый столбец.
Вот пример скрипта для создания таблицы с вычисляемым столбцом:
USE [AdventureWorks] GO -- Создание таблицы с вычисляемым столбцом CREATE TABLE [dbo].[CCtest] ( [empNumb] [int] NULL, [DOBirth] [datetime] NULL, [DORetirement] AS (DATEADD(year, 60, [DOBirth]) - 1) PERSISTED ) GO
Мы также можем создать вычисляемый столбец через SQL Server Management Studio (SSMS), щелкнув правой кнопкой мыши на папке “Tables” в базе данных AdventureWorks и выбрав “Design”. В режиме проектирования мы можем указать детали для вычисляемого столбца.
После создания таблицы с вычисляемым столбцом мы можем вставить данные и протестировать функциональность:
USE AdventureWorks GO INSERT INTO CCTest (empNumb, DOBirth) SELECT 30, '1985-12-13' UNION ALL SELECT 25, '1980-11-18' UNION ALL SELECT 21, '1978-01-19' UNION ALL SELECT 7, '1985-12-13' UNION ALL SELECT 5, '1975-07-23' GO SELECT * FROM dbo.CCTest GO
Мы видим, что вычисляемый столбец [DORetirement] вычисляется на основе столбца [DOBirth] для каждого сотрудника.
Обновление вычисляемых столбцов
Если нам нужно обновить столбец [DOBirth] для конкретного сотрудника, вычисляемый столбец [DORetirement] будет автоматически обновлен:
USE AdventureWorks GO UPDATE CCtest SET DOBirth = '1960-03-25' WHERE empnumb = 25 GO SELECT * FROM dbo.CCTest WHERE Empnumb = 25 GO
Мы видим, что вычисляемый столбец [DORetirement] был обновлен на основе нового значения [DOBirth].
Вычисляемые столбцы с сохранением
При создании вычисляемого столбца мы можем указать свойство “Persisted”. Это свойство позволяет хранить вычисляемый столбец на диске, что может улучшить производительность для определенных операций. Чтобы сделать вычисляемый столбец сохраняемым, он должен быть детерминированным, то есть результат вычисления всегда одинаков для заданного набора входных данных.
Ограничения и соображения
Есть некоторые ограничения и соображения при работе с вычисляемыми столбцами:
- Вы не можете напрямую ссылаться на столбцы из других таблиц в выражении вычисляемого столбца.
- Вы не можете применять операторы вставки или обновления непосредственно к вычисляемым столбцам.
- Если вы объединяете операторы разных типов данных в выражении, оператор с более низким приоритетом будет преобразован в тип с более высоким приоритетом. Если неявное преобразование невозможно, будет сгенерирована ошибка.
- Подзапрос не может быть использован в качестве выражения для создания вычисляемого столбца.
- Вычисляемые столбцы могут использоваться в списке SELECT, в предложениях WHERE или ORDER BY, а также как обычные выражения. Однако, чтобы использовать вычисляемый столбец в качестве ограничения CHECK, FOREIGN KEY или NOT NULL, он должен быть сохраненным.
- Чтобы использовать вычисляемый столбец в качестве ограничения Primary или Unique Key, выражение должно быть детерминированным, а тип данных выражения вычисляемого столбца должен быть индексируемым.
Используя вычисляемые столбцы, мы можем избежать необходимости использования триггеров и ручных вычислений, что приводит к более эффективному и стандартному подходу к обработке вычисляемых значений в SQL Server.