В 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.
