В Microsoft SQL Server в таблицах можно использовать столбцы с вычисляемым значением, они так и называются «Вычисляемые столбцы». Сейчас я расскажу Вам о том, для чего такие столбцы нужны, какие у них особенности и, кончено же, как их создавать.
Вычисляемые столбцы в таблицах
Вычисляемый столбец – это виртуальный столбец таблицы, который вычисляется на основе выражения, в этих выражениях могут участвовать другие столбцы этой же таблицы. Такие столбцы физически не хранятся, их значения рассчитываются каждый раз при обращении к ним. Это поведение по умолчанию, но можно сделать так, чтобы вычисляемые столбцы физически хранились, для этого нужно указать ключевое слово PERSISTED при создании подобного столбца. В данном случае значения данного столбца будут обновляться, когда будут вноситься любые изменения в столбцы, входящие в вычисляемое выражение.
Вычисляемые столбцы нужны для того, чтобы было проще и надежней получить результат каких-то постоянных вычислений. Например, при обращении к таблице, Вы всегда в SQL запросе применяете какую-нибудь формулу (один столбец перемножаете с другим или что-то в этом роде, хотя формула может быть и сложней), так вот, если в таблице определить вычисляемый столбец, указав в его определении нужную формулу, Вам больше не нужно будет каждый раз писать эту формулу в SQL запросе в инструкции SELECT. Вам достаточно обратиться к определенному столбцу (вычисляемому столбцу), который автоматически при выводе значений применяет эту формулу. При этом этот столбец можно использовать в запросах также как обычный столбец, например, в секциях WHEHE (в условии) или в ORDER BY (в сортировке).
Также важно понимать, что вычисляемый столбец не может быть указан в инструкциях INSERT или UPDATE в качестве целевого столбца.
Создание вычисляемого столбца при создании новой таблицы в Microsoft SQL Server
Давайте представим, что нам нужно создать таблицу, в которой будут храниться товары с указанием их количества и цены, при этом мы хотим постоянно знать, на какую сумму у нас того или иного товара, т.е. нам нужно умножить количество на цену. Чтобы этого не делать каждый раз в запросе, мы и создаем вычисляемый столбец, в определении которого указываем соответствующую формулу. Также в данном примере мы укажем ключевое слово PERSISTED, для того чтобы наш столбец хранился физически, а обновлялся, т.е. заново вычислялся, только если изменятся значения в столбцах с количеством или ценой.
Добавление вычисляемого столбца в существующую таблицу в Microsoft SQL Server
А сейчас давайте допустим, что возникла необходимость знать еще и сумму с учетом некого статического коэффициента (например, 1,7). Но нам об этом сказали уже после того, как мы создали таблицу, иными словами, нам нужно добавить вычисляемый столбец в существующую таблицу.
Добавление вычисляемых столбцов доступно также и в графической среде SQL Server Management Studio. Для этого Вам нужно просто в обозревателе объектов найти нужную таблицу и щелкнуть правой кнопкой мыши по контейнеру «Столбцы» и выбрать «Создать столбец», т.е. все как обычно.
Затем указать название столбца и тип данных (тип данных временно), а для того чтобы определить вычисляемый столбец, в «Свойствах столбца» нужно найти раздел «Спецификация вычисляемого столбца» и в поле «Формула» указать соответствующую формулу.
Точно также определяется вычисляемый столбец и при создании таблицы с помощью Management Studio, т.е. просто в соответствующем разделе указывается формула.
Удаление вычисляемого столбца из таблицы
В случае, если Вам больше не нужен вычисляемый столбец, то его можно удалить, делается это также, как и удаление обычного столбца.
На этом мой рассказ про вычисляемые столбцы закончен, в своей книге «Путь программиста T-SQL. Самоучитель по языку Transact-SQL» я подробно рассказываю про все возможности языка T-SQL, удачи!
Здравствуйте, у меня есть таблица Player с несколькими полями, одно из них это DateOfBorn, и у меня есть поле Age, которое должно вычисляться по формуле "текущая дата — DateOfBorn. Если просто написать Select запрос, то все нормально
И если это выполнить просто в запросе, то все вычисляется правильно, но если я подставлю это выражение в значение, которое вычисляется для столбца AGE, то при попытке его сохранения среда пишет, что присутствуют ошибки в этом выражении. Но если заменить вычитаемое, что бы получилось, например, так (datepart(year,getdate())-‘1997’) то в итоге ошибки нет, и при просмотре таблицы, будет отражено "19". Прошу помочь написать правильно вычитаемое, DOB имеет тип "date" т.е. мне нужно при заполнении таблицы вводить только с точностью до дня (указывать еще время рождения в моей области будет бессмысленно). Спасибо!
ОБЛАСТЬ ПРИМЕНЕНИЯ ЭТОЙ СТАТЬИ: .a45b6379-ac63-48d7-ab97-33b3563b36ee(ru-ru).jpeg)
SQL Server (начиная с 2016) База данных SQL Azure .ed423705-b87b-4bee-8f6f-1b9705944575(ru-ru).jpeg)
Хранилище данных SQL Azure Хранилище параллельных данных
Индексы вычисляемых столбцов можно определить, если удовлетворяются следующие требования:
требования к владению;
требования к детерминированности;
требования к точности;
требования к типам данных;
требования к параметру SET.
Требования к владению
Все ссылки на функции в вычисляемом столбце должны иметь того же владельца, что и таблица.
Требования к детерминированности
| Важно |
|---|
Выражения являются детерминированными, если они всегда возвращают один и тот же результат для определенного набора входных данных. Свойство IsDeterministic функции COLUMNPROPERTY определяет, детерминировано ли выражение computed_column_expression.
Выражение computed_column_expression должно быть детерминированным. Выражение computed_column_expression является детерминированным, если соблюдается одно или несколько следующих условий:
Все функции, на которые ссылается выражение, являются детерминированными и точными. Это относится как к пользовательским, так и к встроенным функциям. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions. Функции могут быть неточными, если вычисляемый столбец помечен как PERSISTED. Дополнительные сведения см. ниже в разделе Создание индексов материализованных вычисляемых столбцов .
Все столбцы, на которые ссылается выражение, берутся из таблицы, содержащей вычисляемый столбец.
Ни одна ссылка на столбец не запрашивает данные из нескольких строк. Например, агрегатные функции, такие как SUM или AVG, используют данные из нескольких строк и делают выражение computed_column_expression недетерминированным.
У выражения computed_column_expression нет доступа к системным данным или данным пользователя.
Для индексирования требуется, чтобы любой вычисляемый столбец, содержащий выражение CLR, перед индексированием был детерминированным и помеченным как PERSISTED. Выражения определяемого пользователем типа данных CLR допускаются в определениях вычисляемых столбцов. Вычисляемые столбцы, которые имеют определяемый пользователем тип данных CLR, могут быть индексированы, если этот тип сопоставим. Дополнительные сведения об определяемых пользователем типах данных CLR см. в разделе Определяемые пользователем типы данных CLR.
| Примечание |
|---|
При указании в индексируемых вычисляемых столбцах SQL Server строковых литералов типа данных «дата», рекомендуется явно преобразовывать их в тип данных «дата» при помощи детерминированного стиля форматирования даты. Список детерминированных стилей форматирования даты см. в разделе CAST и CONVERT. Выражения, включающие неявные преобразования символьных строк в типы данных, считаются недетерминированными, если только не установлен уровень совместимости базы данных, равный 80 или менее. Это связано с тем, что результаты зависят от значений параметров LANGUAGE и DATEFORMAT , определенных для сеанса сервера. Например, результат выражения CONVERT (datetime, ’30 listopad 1996′, 113) зависит от значения параметра LANGUAGE, поскольку строка 30 listopad 1996 в различных языках обозначает разные месяцы. Аналогично, в выражении DATEADD(mm,3,’2000-12-01′) компонент Компонент Database Engine интерпретирует строку ‘2000-12-01’ в соответствии со значением параметра DATEFORMAT.
Неявное преобразование символьных данных не в Юникоде между различными параметрами сортировки также считается недетерминированным, если уровень совместимости не установлен равным или меньшим 80.
Если параметр уровня совместимости равен 90, создавать индексы на вычисляемых столбцах, содержащих эти выражения, нельзя. Это, однако, не относится к существующим вычисляемым столбцам, содержащим такие выражения из обновленной базы данных. Если используются индексированные вычисляемые столбцы, которые содержат неявные преобразования из строк в даты, то для предотвращения возможного повреждения индекса убедитесь, что параметры LANGUAGE и DATEFORMAT согласованы с базами данных и приложениями.
Требования к точности
Выражение computed_column_expression должно быть точным. Выражение computed_column_expression является точным, если соблюдается одно или несколько следующих условий:
Выражение не относится к типу данных float или real .
В его определении не используется тип данных float или real . Например, в следующей инструкции столбец y имеет тип int и является детерминированным, но неточным.
| Примечание |
|---|