Как сделать вычисляемое поле в sql

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

Вычисляемый столбец представляет собой виртуальный столбец, физически не хранящийся в таблице, если для него не установлен признак PERSISTED. В выражении вычисляемого столбца для вычисления значения могут использоваться данные из других столбцов. Вы можете указать выражение для вычисляемого столбца в SQL Server с помощью SQL Server Management Studio (SSMS) или Transact-SQL (T-SQL).

Ограничения

• Вычисляемый столбец нельзя использовать в качестве определения ограничения DEFAULT или FOREIGN KEY или с определением ограничения NOT NULL. Однако если вычисляемый столбец определен детерминированным выражением и тип данных результата допускается для индексных столбцов, то вычисляемый столбец может быть использован как ключевой столбец в индексе или как часть ограничений PRIMARY KEY или UNIQUE. Например, если в таблице есть целые столбцы a и b, вычисляемый столбец a + b может быть индексирован, но вычисляемый столбец + DATEPART(dd, GETDATE()) не может быть индексирован, так как значение может измениться в последующих вызовах.
• Вычисляемый столбец не может быть целевым столбцом инструкций INSERT или UPDATE.
• SET QUOTED_IDENTIFIER при создании или изменении индексов в вычисляемых столбцах или индексированных представлениях должно быть включено. Дополнительные сведения см. в статье SET QUOTED_IDENTIFIER (Transact-SQL).

Разрешения

Требуется разрешение ALTER на таблицу.

Использование среды SQL Server Management Studio

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

1. В обозревателе объектовразверните таблицу, в которую нужно добавить новый вычисляемый столбец. Щелкните правой кнопкой мыши Столбцы и выберите Создать столбец.
2. Введите имя столбца и выберите тип данных по умолчанию (nchar(10)). Ядро СУБД определяет тип данных вычисляемого столбца, применяя правила приоритета типа данных к выражениям, указанным в формуле. Например, если формула ссылается на столбец типа money и столбец типа int, то вычисляемый столбец имеет тип money , поскольку этот тип данных имеет более высокий приоритет. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).
3. На вкладке Свойства столбца раскройте свойство Спецификация вычисляемого столбца .
4. В дочернем свойстве (Формула) введите выражение для этого столбца в ячейку сетки справа. Например, в столбце SalesTotal можно ввести формулу SubTotal+TaxAmt+Freight , чтобы добавить значения в этих столбцах для каждой строки в таблице.

Внимание Если формула связывает два выражения различных типов данных, то по правилам приоритета типов данных определяется, какой тип данных имеет меньший приоритет и будет преобразован в тип данных с большим приоритетом. Если преобразование не поддерживается неявным преобразованием, возвращается ошибка Error validating the formula for column column_name. . Используйте функцию CAST или CONVERT, чтобы устранить конфликт типа данных. Например, если столбец типа nvarchar объединяется со столбцом типа int, то целочисленный тип необходимо преобразовать в nvarchar , как показано в следующей формуле: (‘Prod’+CONVERT(nvarchar(23),ProductID)) . Дополнительные сведения см. в разделе Функции CAST и CONVERT (Transact-SQL).

Добавление определения вычисляемого столбца к существующему столбцу

1. В обозревателе объектовщелкните правой кнопкой мыши таблицу со столбцом, определение которого необходимо изменить, и разверните папку Столбцы .
2. Щелкните правой кнопкой мыши столбец, для которого необходимо задать формулу вычисляемого столбца, и выберите пункт Удалить. Нажмите ОК.
3. Добавьте новый столбец и укажите формулу вычисляемого столбца в соответствии с предыдущей процедурой, чтобы добавить новый вычисляемый столбец.

Использование Transact-SQL

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

В следующем примере создается таблица с вычисляемым столбцом, который умножает значение столбца QtyAvailable на значение, указанное в столбце UnitPrice .

CREATE TABLE dbo.Products ( ProductID int IDENTITY (1,1) NOT NULL , QtyAvailable smallint , UnitPrice money , InventoryValue AS QtyAvailable * UnitPrice ); -- Insert values into the table. INSERT INTO dbo.Products (QtyAvailable, UnitPrice) VALUES (25, 2.00), (10, 1.5); -- Display the rows in the table. SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue FROM dbo.Products; -- Update values in the table. UPDATE dbo.Products SET UnitPrice = 2.5 WHERE ProductID = 1; -- Display the rows in the table, and the new values for UnitPrice and InventoryValue. SELECT ProductID, QtyAvailable, UnitPrice, InventoryValue FROM dbo.Products; 

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

В следующем примере в таблицу, созданную в предыдущем примере, будет добавлен новый столбец.

ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5); 

При необходимости добавьте аргумент PERSISTED, чтобы физически хранить вычисляемые значения в таблице:

ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5) PERSISTED; 

Замена существующего столбца на вычисляемый столбец

В следующем примере изменяется столбец, добавленный в предыдущем примере.

ALTER TABLE dbo.Products DROP COLUMN RetailValue; GO ALTER TABLE dbo.Products ADD RetailValue AS (QtyAvailable * UnitPrice * 1.5); GO 

Далее

• Инструкция ALTER TABLE (Transact-SQL)
• ALTER TABLE computed_column_definition (Transact-SQL)

SQL-Урок 6. Расчетные (вычислительные) поля

Зачем нужно использовать расчетные поля? Как правило, информация в БД представлена ​​в разрезе отдельных фрагментов, так как легче структуризировать данные и оперировать ими. Однако нам часто нужно использовать не отдельные части данных, а уже объединенную и обработанную информацию. Например, часто необходимо сочетать имя и фамилию клиентов, сочетать элементы адресов, которые находятся в разных столбцах таблицы, обрабатывать текст и отдельные слова, буквы и символы, суммировать общую стоимость покупки, отображать статистику по информации, находящейся в БД. Данные обычно хранятся отдельными «кусками», что требует их дополнительной обработки на стороне клиентской программы. Однако есть возможность получать уже обработанную информацию посредством СУБД. Именно в этом случае помогают расчетные поля. Они автоматически создаются при выполнении запроса и имеют вид и свойства обычных столбцов, уже имеющихся в таблице. Единственное отличие состоит в том, что физически расчетных полей нет, поэтому они не занимают дополнительное место в БД, а временно существуют в «оперативной памяти» СУБД. Преимуществом выполнения операций на стороне СУБД является быстрота обработки данных.

1. Выполнение математических операций

Одним из способов использования расчетных полей является выполнение математических операций над выбранными данными. Давайте на примере рассмотрим как это происходит, использовав снова нашу таблицу Sumproduct. Предположим, на нужно рассчитать среднюю цену приобретения каждого товара. Для этого нужно переделить колонку Amount (сумма) на Quantity (количество):

Run SQLSELECT DISTINCT Product, Amount/Quantity FROM Sumproduct 

Try it Yourself

Как видим, СУБД отобрала все наименования товаров и отразила их среднюю стоимость в отдельном столбце, созданном при выполнении запроса. Также можно заметить, что мы использовали дополнительный оператор DISTINCT, который нужен нам для отображения уникальных названий товаров (без него мы бы получили дублирование записей).

2. Использование псевдонимов

В предыдущем примере мы рассчитывали среднюю стоимость покупки каждого товара и отразили значение в расчетном столбце. Однако в дальнейшем, нам будет неудобно обращаться к этому полю, поскольку его название является неинформативным для нас (СУБД дала название полю — Expr1001). Однако мы можем назвать поле самостоятельно, заранее указав его название в запросе, то есть дать псевдоним. Давайте перепишем предыдущий пример и укажем псевдоним для расчетного поля:

Run SQLSELECT DISTINCT Product, Amount/Quantity AS AvgPrice FROM Sumproduct 

Try it Yourself

Видимо, наше расчетное поле получило название AvgPrice. Для этого мы использовали оператор AS, после которого указали необходимое название. Следует отметить, что в SQL поддерживаются только основные математические операции: добавление (+), вычитание (-), умножение (*), деление (/). Также для смены очередности выполнения операции можно использовать круглые скобки.

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

3. Соединение полей (конкатенация)

Помимо математических операций, мы также можем сочетать текст и выводить его в отдельном поле. Давайте рассмотрим, как можно осуществить склеивание (конкатенацию) текста. Для соединения текста из разных колонок в MS Access используется оператор «плюс» (+), например:

Run SQLSELECT Month + ' ' + Product AS NewField, Quantity FROM Sumproduct 

Try it Yourself

В этом примере мы соединили значение двух столбцов и вывели результат в новое поле NewField.

Оператор плюс (+) не поддерживается в диалекте MySQL для соединения (конкатенации) текста из нескольких колонок. В этом случае используйте функцию CONCAT().

• Изменение регистра букв в тексте
• Сумма прописью на украинском языке
• Поиск латиницы в кириллице и наоборот
• Транслитерация с украинского на английский

Базовый курс SQL. Вычисляемые поля

Иногда нам нужно извлечь данные не в том формате, в котором они хранятся в таблицах. Например:

• Соединить ФИО, хранящиеся в разных столбцах
• Вычислить стоимость покупки на основе цены товара и количества
• Скомбинировать строку адреса
• Сумма, среднее значение и тп.

Следует знать, что в таком случае SQL предоставляет возможность произвести некоторые преобразования с данными прямо в процессе запроса. Отформатировать полученные «сырые» данные могла бы и клиентская сторона, но как правило, на сервере базы данных это происходит гораздо быстрее.

Вычисляемые поля — это «виртуальные» поля (столбцы) таблицы, не существующие в БД, а создаваемые для нужд пользователя в процессе запроса оператором SELECT.

Конкатенация полей

Конкатенация — «склеивание» нескольких строк в одну.

Допустим, мы создаём список студентов участвующих в творческом конкурсе, и нам требуется указать их возраст в виде Фамилия (возраст). В PostgreSQL соединить значения двух столбцов и добавить скобки мы можем с помощью оператора ||:

SELECT student_surname || ' (' || student_age || ')' FROM Students ORDER BY student_surname;

------------------------------------ Адамченко (21 ) Грошев (20 ) Егорова (19 ) Колобков (22 ) Легран (24 ) Петрашевский (22 ) Распопов (22 ) Римский (21 ) Сейдинай (25 ) Шульгина (23 )

Как вы можете увидеть, в результате мы имеем все 4 части, склеенные в одну строку. Но мешают пробелы, которыми было заполнено поле. Чтобы их убрать, воспользуемся функцией RTRIM(), которая удаляет все пробелы, справа от значения. Также, в случае необходимости, можете использовать LTRIM() и TRIM(), удаляющие соответственно пробелы слева от строки или пробелы и слева, и справа.

SELECT RTRIM(student_surname) || ' (' || RTRIM(student_age) || ')' FROM Students ORDER BY student_surname; ------------------------------------ Адамченко (21) Грошев (20) Егорова (19) Колобков (22) Легран (24) Петрашевский (22) Распопов (22) Римский (21) Сейдинай (25) Шульгина (23)

В некоторых других СУБД для конкатенации вместо «||» используется «+».
В MySQL конкатенацию можно осуществить с помощью функции CONCAT().

Псевдонимы вычисляемых полей

Наверное вы заметили, что новый столбец, который мы получили «на лету», не имеет имени. В таком случае мы не сможем обратиться к нему на стороне клиентского приложения. Чтобы решить эту проблему, дадим столбцу псевдоним. Для этого используется ключевое слово AS:

SELECT RTRIM(student_surname) || ' (' || RTRIM(student_age) || ')' AS student_data FROM Students ORDER BY student_surname;

student_data ------------------------------------ Адамченко (21) Грошев (20) Егорова (19) Колобков (22) Легран (24) Петрашевский (22) Распопов (22) Римский (21) Сейдинай (25) Шульгина (23)

Теперь мы сможем обращаться к результату данного запроса по имени, так, как если бы это был реальный столбец.

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

Математические операции

Теперь нам нужно определить победителей конкурса. Для этого сложим результаты двух туров и отсортируем список по убыванию:

SELECT student_id, first_round_points, second_round_points, first_round_points + second_round_points AS final_points FROM Participants ORDER BY final_points DESC;

Получим практически готовую турнирную таблицу:

student_id | first_round_points | second_round_points | final_points ------------------------------------------------------------------------------- 92540 | 148 | 115 | 263 92522 | 95 | 124 | 219 92518 | 103 | 59 | 162 92435 | 65 | 89 | 154 92526 | 13 | 36 | 49

В данном случае столбец final_points является вычисляемым полем. В SQL на ряду со сложением (+) могут быть использованы вычитание (-), умножение (*) и деление (/). Для управления порядком вычислений используйте скобки.

Есть и другие способы расчёта суммы значений в SQL, например, с помощью функции SUM().
Подробнее функции мы рассмотрим в следующем разделе.

Key Words for FKN + antitotal forum (CS VSU):

• sql базовый курс
• sql как начать учить
• sql как стать программистом
• sql вычисляемые поля
• sql trim
• sql rtrim
• sql ltrim
• sql сложить два поля
• sql конкатенация полей
• sql на лету

Производительность вычисляемых столбцов в SQL Server

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

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

Часто несохраняемые (non-persistent) вычисляемые столбцы создают большую нагрузку на процессор, что приводит к замедлению запросов и зависанию приложений. К счастью, SQL Server предоставляет несколько способов улучшения производительности вычисляемых столбцов. Можно создавать сохраняемые (persisted) вычисляемые столбцы, индексировать их или делать и то и другое.

Для демонстрации я создал четыре похожие таблицы и заполнил их идентичными данными, полученными из демонстрационной базы данных WideWorldImporters. В каждой таблице есть одинаковый вычисляемый столбец, но в двух таблицах он сохраняемый, а в двух — с индексом. В результате получаются следующие варианты:

• Таблица Orders1 — несохраняемый вычисляемый столбец.
• Таблица Orders2 — сохраняемый вычисляемый столбец.
• Таблица Orders3 — несохраняемый вычисляемый столбец с индексом.
• Таблица Orders4 — сохраняемый вычисляемый столбец с индексом.

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

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

Однако следует отметить, что пользовательские функции могут создать свои проблемы с производительностью. Если таблица содержит вычисляемый столбец с функцией, то Query Engine не будет использовать параллелизм (только если вы не используете SQL Server 2019). Даже в ситуации, если вычисляемый столбец не указан в запросе. Для большого набора данных это может сильно влиять на производительность. Функции также могут замедлять выполнение UPDATE и влиять на то, как оптимизатор вычисляет стоимость запроса к вычисляемому столбцу. Это не значит, что вы никогда не должны использовать функции в вычисляемом столбце, но определенно к этому следует относиться с осторожностью.

Независимо от того, используете вы функции или нет, создание несохраняемого вычисляемого столбца довольно просто. Следующая инструкция CREATE TABLE определяет таблицу Orders1 , которая включает в себя вычисляемый столбец Cost .

USE WideWorldImporters; GO DROP TABLE IF EXISTS Orders1; GO CREATE TABLE Orders1( LineID int IDENTITY PRIMARY KEY, ItemID int NOT NULL, Quantity int NOT NULL, Price decimal(18, 2) NOT NULL, Profit decimal(18, 2) NOT NULL, Cost AS (Quantity * Price - Profit)); INSERT INTO Orders1 (ItemID, Quantity, Price, Profit) SELECT StockItemID, Quantity, UnitPrice, LineProfit FROM Sales.InvoiceLines WHERE UnitPrice IS NOT NULL ORDER BY InvoiceLineID;

Чтобы определить вычисляемый столбец, укажите его имя с последующим ключевым словом AS и выражением. В нашем примере мы умножаем Quantity на Price и вычитаем Profit . После создания таблицы заполняем ее с помощью INSERT, используя данные из таблицы Sales.InvoiceLines базы данных WideWorldImporters. Далее выполняем SELECT.

SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;

Этот запрос должен вернуть 22 973 строки или все строки, которые есть у вас в базе данных WideWorldImporters. План выполнения этого запроса показан на рисунке 1.

Рисунок 1. План выполнения запроса к таблице Orders1

Первое, что следует отметить — это сканирование кластерного индекса (Clustered Index Scan), что не является эффективным способом получения данных. Но это не единственная проблема. Давайте посмотрим на количество логических чтений (Actual Logical Reads) в свойствах Clustered Index Scan (см. рисунок 2).

Рисунок 2. Логические чтения для запроса к таблице Orders1

Количество логических чтений (в данном случае 1108) — это количество страниц, которые прочитаны из кэша данных. Цель состоит в том, чтобы попытаться максимально уменьшить это число. Поэтому полезно его запомнить и сравнить с другими вариантами.

Количество логических чтений можно также получить, запустив инструкцию SET STATISTICS IO ON перед выполнением SELECT. Для просмотра процессорного и общего времени — SET STATISTICS TIME ON или посмотреть свойства оператора SELECT в плане выполнения запроса.

Еще один момент, на который стоит обратить внимание — в плане выполнения присутствуют два оператора Compute Scalar. Первый (тот, что справа) — это вычисление значения вычисляемого столбца для каждой возвращаемой строки. Поскольку значения столбцов вычисляются на лету, вы не можете избежать этого шага с несохраняемыми вычисляемыми столбцами, если только не создадите индекс на этом столбце.

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

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

Один из методов, часто используемых для решения проблем с производительностью, — это определение вычисляемого столбца как сохраняемого (persisted). При таком подходе выражение вычисляется заранее и результат сохраняется вместе с остальными данными таблицы.

Чтобы столбец можно было сделать сохраняемым, он должен быть детерминированным, то есть выражение должно всегда возвращать один и тот же результат при одинаковых входных данных. Например, вы не можете использовать функцию GETDATE в выражении столбца, потому что возвращаемое значение всегда изменяется.

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

DROP TABLE IF EXISTS Orders2; GO CREATE TABLE Orders2( LineID int IDENTITY PRIMARY KEY, ItemID int NOT NULL, Quantity int NOT NULL, Price decimal(18, 2) NOT NULL, Profit decimal(18, 2) NOT NULL, Cost AS (Quantity * Price - Profit) PERSISTED); INSERT INTO Orders2 (ItemID, Quantity, Price, Profit) SELECT StockItemID, Quantity, UnitPrice, LineProfit FROM Sales.InvoiceLines WHERE UnitPrice IS NOT NULL ORDER BY InvoiceLineID;

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

sp_spaceused 'Orders1'; GO sp_spaceused 'Orders2'; GO

На рисунке 3 показан результат выполнения этой хранимой процедуры. Объем данных в таблице Orders1 составляет 8 824 КБ, а в таблице Orders2 — 12 936 КБ. На 4 112 КБ больше, что необходимо для хранения вычисленных значений.

Рисунок 3. Сравнение размера таблиц Orders1 и Orders2

Хотя эти примеры основаны на довольно небольшом наборе данных, но вы можете видеть, как количество хранимых данных может быстро увеличиваться. Тем не менее это может быть компромиссом, если производительность улучшается.

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

SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;

Это тот же SELECT, который я использовал для таблицы Orders1 (за исключением изменения имени). На рисунке 4 показан план выполнения.

Рисунок 4. План выполнения запроса к таблице Orders2

Здесь также все начинается с Clustered Index Scan. Но на этот раз, есть только один оператор Compute Scalar, потому что вычисляемые столбцы больше не нужно вычислять во время выполнения. В общем случае чем меньше шагов, тем лучше. Хотя это и далеко не всегда так.

Второй запрос генерирует 1593 логических чтения, что на 485 больше по сравнению с 1108 чтений для первой таблицы. Несмотря на это, он выполняется быстрее, чем первый. Хотя и только примерно на 100 мс, а иногда и намного меньше. Процессорное время также уменьшилось, но тоже не на много. Скорее всего, разница была бы гораздо больше на больших объемах и более сложных вычислениях.

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

Другой метод, который обычно используется для улучшения производительности вычисляемого столбца, — это индексирование. Для возможности создания индекса столбец должен быть детерминированным и точным, что означает, что выражение не может использовать типы float и real (если столбец несохраняемый). Существуют также ограничения и для других типов данных, а также на параметры SET. Полный перечень ограничений см. в документации SQL Server Indexes on Computed Columns (Индексы на вычисляемых столбцов).

Проверить подходит ли несохраняемый вычисляемый столбец для индексирования можно через его свойства. Для просмотра свойств воспользуемся функцией COLUMNPROPERTY . Нам важны свойства IsDeterministic, IsIndexable и IsPrecise.

DECLARE @id int = OBJECT_ID('dbo.Orders1') SELECT COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic', COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable', COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';

Оператор SELECT должен возвращать значение 1 для каждого свойства, чтобы вычисляемый столбец мог быть проиндексирован (см. рисунок 5).

Рисунок 5. Проверка возможности создания индекса

После проверки вы можете создать некластерный индекс. Вместо изменения таблицы Orders1 я создал третью таблицу ( Orders3 ) и включил индекс в определение таблицы.

DROP TABLE IF EXISTS Orders3; GO CREATE TABLE Orders3( LineID int IDENTITY PRIMARY KEY, ItemID int NOT NULL, Quantity int NOT NULL, Price decimal(18, 2) NOT NULL, Profit decimal(18, 2) NOT NULL, Cost AS (Quantity * Price - Profit), INDEX ix_cost3 NONCLUSTERED (Cost, ItemID)); INSERT INTO Orders3 (ItemID, Quantity, Price, Profit) SELECT StockItemID, Quantity, UnitPrice, LineProfit FROM Sales.InvoiceLines WHERE UnitPrice IS NOT NULL ORDER BY InvoiceLineID;

Я создал некластерный покрывающий индекс, который включает оба столбца ItemID и Cost из запроса SELECT. После создания, заполнения таблицы и индекса можно выполнить следующую инструкцию SELECT, аналогичную предыдущим примерам.

SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;

На рисунке 6 показан план выполнения этого запроса, который теперь использует некластерный индекс ix_cost3 (Index Seek), а не выполняет сканирование кластерного индекса.

Рисунок 6. План выполнения запроса к таблице Orders3

Если вы посмотрите свойства оператора Index Seek, то обнаружите, что запрос теперь выполняет только 92 логических чтения, а в свойствах оператора SELECT увидите, что процессорное и общее время стало меньше. Разница несущественная, но, опять же, здесь небольшой набор данных.

Следует также отметить, что в плане выполнения присутствует только один оператор Compute Scalar, а не два, как было в первом запросе. Поскольку вычисляемый столбец проиндексирован, то значения уже вычислены. Это устраняет необходимость вычисления значений во время выполнения, даже если столбец не был определен как сохраняемый.

Индекс на сохраняемом столбце

Вы также можете создать индекс для сохраняемого вычисляемого столбца. Хотя это приведет к хранению дополнительных данных и данных индекса, но в некоторых случаях может быть полезно. Например, вы можете создать индекс для сохраняемого вычисляемого столбца, даже если он использует типы данных float или real. Этот подход также может быть полезен при работе с функциями CLR, и когда нельзя проверить, являются ли функции детерминированными.

Следующая инструкция CREATE TABLE создает таблицу Orders4 . Определение таблицы включает в себя как сохраняемый столбец Cost , так и некластерный покрывающий индекс ix_cost4.

DROP TABLE IF EXISTS Orders4; GO CREATE TABLE Orders4( LineID int IDENTITY PRIMARY KEY, ItemID int NOT NULL, Quantity int NOT NULL, Price decimal(18, 2) NOT NULL, Profit decimal(18, 2) NOT NULL, Cost AS (Quantity * Price - Profit) PERSISTED, INDEX ix_cost4 NONCLUSTERED (Cost, ItemID)); INSERT INTO Orders4 (ItemID, Quantity, Price, Profit) SELECT StockItemID, Quantity, UnitPrice, LineProfit FROM Sales.InvoiceLines WHERE UnitPrice IS NOT NULL ORDER BY InvoiceLineID;

После того как таблица и индекс созданы и заполнены, выполним SELECT.

SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;

На рисунке 7 показан план выполнения. Как и в предыдущем примере, запрос начинается с поиска по некластерному индексу (Index Seek).

Рисунок 7. План выполнения запроса к таблице Orders4

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

sp_spaceused 'Orders1'; GO sp_spaceused 'Orders2'; GO sp_spaceused 'Orders3'; GO sp_spaceused 'Orders4'; GO

Результаты показаны на рисунке 8. Как и ожидалось, в сохраняемых вычисляемых столбцах больше объем данных, а в индексированных — больше объем индексов.

Рисунок 8. Сравнение использования пространства для всех четырех таблиц

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

Работа с вычисляемыми столбцами в SQL Server

Вычисляемый столбец не является обычным столбцом таблицы, и с ним следует обращаться с осторожностью, чтобы не ухудшить производительность. Большинство проблем с производительностью можно решить через сохранение или индексацию столбца, но в обоих подходах необходимо учитывать дополнительное дисковое пространство и то, как изменяются данные. При изменении данных значения вычисляемого столбца должны быть обновлены в таблице или индексе или в обоих местах, если вы проиндексировали сохраняемый вычисляемый столбец. Решить, какой из вариантов лучше подходит, можно только для вашего конкретного случая. И, скорее всего, вам придется использовать все варианты.

Читать ещё