Вычисляемые столбцы в Transact-SQL

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

Вычисляемые столбцы в таблицах

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

Вычисляемые столбцы нужны для того, чтобы было проще и надежней получить результат каких-то постоянных вычислений. Например, при обращении к таблице, Вы всегда в SQL запросе применяете какую-нибудь формулу (один столбец перемножаете с другим или что-то в этом роде, хотя формула может быть и сложней), так вот, если в таблице определить вычисляемый столбец, указав в его определении нужную формулу, Вам больше не нужно будет каждый раз писать эту формулу в SQL запросе в инструкции SELECT. Вам достаточно обратиться к определенному столбцу (вычисляемому столбцу), который автоматически при выводе значений применяет эту формулу. При этом этот столбец можно использовать в запросах также как обычный столбец, например, в секциях WHEHE (в условии) или в ORDER BY (в сортировке).

Также важно понимать, что вычисляемый столбец не может быть указан в инструкциях INSERT или UPDATE в качестве целевого столбца.

Создание вычисляемого столбца при создании новой таблицы в Microsoft SQL Server

Давайте представим, что нам нужно создать таблицу, в которой будут храниться товары с указанием их количества и цены, при этом мы хотим постоянно знать, на какую сумму у нас того или иного товара, т.е. нам нужно умножить количество на цену. Чтобы этого не делать каждый раз в запросе, мы и создаем вычисляемый столбец, в определении которого указываем соответствующую формулу. Также в данном примере мы укажем ключевое слово PERSISTED, для того чтобы наш столбец хранился физически, а обновлялся, т.е. заново вычислялся, только если изменятся значения в столбцах с количеством или ценой.

Примечание! Все примеры выполнены в Microsoft SQL Server 2016 Express.

   
   --Создание таблицы с вычисляемым столбцом
   CREATE TABLE TestTable (
        [ProductId] [INT] IDENTITY(1,1) NOT NULL,
        [ProductName] [VARCHAR](100) NOT NULL,
        [Quantity] [SMALLINT] NULL,
        [Price] [Money] NULL,
        [Summa] AS ([Quantity] * [Price]) PERSISTED --Вычисляемый столбец
   )

   --Добавление данных в таблицу
   INSERT INTO TestTable
        VALUES ('Портфель', 1, 500),
                   ('Карандаш', 5, 20),
                   ('Тетрадь', 10, 50)

   --Выборка данных
   SELECT * FROM TestTable

Добавление вычисляемого столбца в существующую таблицу в Microsoft SQL Server

А сейчас давайте допустим, что возникла необходимость знать еще и сумму с учетом некого статического коэффициента (например, 1,7). Но нам об этом сказали уже после того, как мы создали таблицу, иными словами, нам нужно добавить вычисляемый столбец в существующую таблицу.

   
   --Добавление вычисляемого столбца в таблицу
   ALTER TABLE TestTable ADD SummaALL AS ([Quantity] * [Price] * 1.7);

   --Выборка данных
   SELECT * FROM TestTable

Добавление вычисляемых столбцов доступно также и в графической среде SQL Server Management Studio. Для этого Вам нужно просто в обозревателе объектов найти нужную таблицу и щелкнуть правой кнопкой мыши по контейнеру «Столбцы» и выбрать «Создать столбец», т.е. все как обычно.

Затем указать название столбца и тип данных (тип данных временно), а для того чтобы определить вычисляемый столбец, в «Свойствах столбца» нужно найти раздел «Спецификация вычисляемого столбца» и в поле «Формула» указать соответствующую формулу.

Точно также определяется вычисляемый столбец и при создании таблицы с помощью Management Studio, т.е. просто в соответствующем разделе указывается формула.

Удаление вычисляемого столбца из таблицы

В случае, если Вам больше не нужен вычисляемый столбец, то его можно удалить, делается это также, как и удаление обычного столбца.

   
   ALTER TABLE TestTable DROP COLUMN SummaALL;

На этом мой рассказ про вычисляемые столбцы закончен, всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL, удачи!

Заметка! Для профессионального изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL.