Поделиться через

Улучшения SQL Server и База данных SQL Azure при обработке некоторых типов данных и необычных операций

  • Статья

В этой статье описывается, как сохраненные структуры в базе данных SQL Server можно проверить в рамках уровня совместимости обновления и как все затронутые структуры можно перестроить после обновления уровня совместимости.

Исходная версия продукта: SQL Server 2017, SQL Server 2016
Исходный номер базы знаний: 4010261

Ядро СУБД в Microsoft SQL Server 2016 и База данных SQL Azure включает улучшения преобразования типов данных и несколько других операций. Большинство этих улучшений обеспечивают повышенную точность при работе с типами с плавающей запятой, а также с классическими типами даты и времени.

Эти улучшения доступны при использовании уровня совместимости базы данных не менее 130. Это означает, что для некоторых (в основном редких) выражений можно увидеть различные результаты для некоторых входных значений после обновления базы данных до уровня совместимости 130 или более поздней. Эти результаты могут отражаться в следующих результатах:

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

Если у вас есть база данных, созданная в более ранней версии SQL Server, рекомендуется выполнить дополнительную проверку после обновления до SQL Server 2016 или более поздней версии и перед изменением уровня совместимости базы данных.

Если вы обнаружите, что какие-либо сохраненные структуры в базе данных затронуты этими изменениями, рекомендуется перестроить затронутые структуры после обновления уровня совместимости базы данных. Благодаря этому вы сможете воспользоваться этими улучшениями в SQL Server 2016 или более поздней версии.

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

Этапы проверки во время обновления до уровня совместимости базы данных

Начиная с SQL Server 2016, SQL Server и База данных SQL Azure включают улучшения точности следующих операций:

  • Необычные преобразования типов данных. К ним относятся следующие:
    • Float/integer to/from datetime/smalldatetime
    • Real/float to/from numeric/money/smallmoney
    • Float to real
  • Некоторые случаи DATEPART/DATEDIFF и DEGREES
  • CONVERT с использованием NULL стиля

Чтобы использовать эти улучшения для оценки выражений в приложении, измените уровень совместимости баз данных на 130 (для SQL Server 2016) или 140 (для SQL Server 2017 и База данных SQL Azure). Дополнительные сведения обо всех изменениях и некоторых примерах, показывающих изменения, см. в разделе "Приложение A ".

Следующие структуры в базе данных могут сохранять результаты выражения:

  • Табличные данные, подверженные ограничениям CHECK
  • Сохраненные вычисляемые столбцы
  • Индексы, использующие вычисляемые столбцы в ключе или включенные столбцы
  • Отфильтрованные индексы
  • Индексированные представления

Рассмотрим следующий сценарий:

  • У вас есть база данных, созданная более ранней версией SQL Server или уже созданная в SQL Server 2016 или более поздней версии, но на уровне совместимости 120 или более ранней версии.

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

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

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

Примечание.

Флаг трассировки 139 в SQL Server

Глобальный флаг трассировки 139 представлен в SQL Server 2016 CU3 и пакете обновления 1 (SP) для принудительной правильной семантики преобразования в области команд проверки DBCC, таких как DBCC CHECKDB, DBCC CHECKTABLEи DBCC CHECKCONSTRAINTS при анализе улучшенной точности и логики преобразования, представленной с уровнем совместимости 130 на базе данных с более ранним уровнем совместимости.

Предупреждение

Флаг трассировки 139 не предназначен для непрерывной включения в рабочей среде и должен использоваться исключительно для выполнения проверок базы данных, описанных в этой статье. Поэтому его следует отключить с помощью dbcc traceoff (139, -1) в одном сеансе после завершения проверок проверки.

Флаг трассировки 139 поддерживается начиная с SQL Server 2016 CU3 и SQL Server 2016 с пакетом обновления 1 (SP1).

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

  1. Выполните проверку, чтобы определить все затронутые сохраненные структуры:
    1. Включите флаг трассировки 139, выполнив команду DBCC TRACEON(139, -1).
    2. Выполнение DBCC CHECKDB/TABLE и CHECKCONSTRAINTS команды.
    3. Отключите флаг трассировки 139, выполнив команду DBCC TRACEOFF(139, -1).
  2. Измените уровень совместимости базы данных на 130 (для SQL Server 2016) или 140 (для SQL Server 2017 и База данных SQL Azure).
  3. Перестройте все структуры, определенные на шаге 1.

Примечание.

Флаги трассировки в База данных SQL Azure Настройка флагов трассировки не поддерживаются в База данных SQL Azure. Поэтому перед выполнением проверки необходимо изменить уровень совместимости:

  1. Обновите уровень совместимости базы данных до 140.
  2. Проверьте, чтобы определить все затронутые сохраненные структуры.
  3. Перестройте структуры, определенные на шаге 2.

  • Приложение A содержит подробный список всех улучшений точности и пример для каждого из них.

  • Приложение B содержит подробный пошаговый процесс для выполнения проверки и перестроения всех затронутых структур.

  • Приложение C и Приложение D содержат скрипты, помогающие определить потенциально затронутые объекты в базе данных. Таким образом, можно задать область проверки и создать соответствующие скрипты для выполнения проверок. Чтобы с легкостью определить, влияют ли все сохраненные структуры в базах данных на повышение точности на уровне совместимости 130, запустите скрипт в приложении D , чтобы создать правильные проверки проверки, а затем запустить этот скрипт для проверки.

Приложение A. Изменения уровня совместимости 130

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

В следующих таблицах перечислены преобразования типов данных и дополнительные операции.

Преобразования типов данных

С дт. По Изменение Пример запроса Результат для уровня < совместимости 130 Результат для уровня совместимости = 130
float, real, numeric, decimal, money или smallmoney datetime или smalldatetime Увеличьте точность округления. Ранее, день и время были преобразованы отдельно, и результаты были усечены перед их объединением. DECLARE @f FLOAT = 1.2 DECLARE @d DATETIME = @f SELECT CAST(@d AS FLOAT) 1.19999996141975 1.2
datetime bigint, int, or smallint Отрицательное время даты, время которого составляет ровно полдня или в тике половины дня округляется неправильно (результат отключается на 1). DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT) 0 -1
datetime или smalldatetime float, real, numeric, money, or smallmoney Улучшена точность для последних 8 бит точности в некоторых случаях. DECLARE @p0 DATETIME = '1899-12-31 23:58:00.470' DECLARE @f FLOAT = CONVERT(FLOAT, @p0) SELECT @f, CAST(@f AS VARBINARY(8)) -0.0013834907407406, 0xBF56AA9B21D85800 -0.0013834907407407, 0xBF56AA9B21D8583B
float real Проверки границ менее строги. SELECT CAST (3.40282347000E+038 AS REAL) Переполнение при арифметической операции 3.402823E+38
numeric, money и smallmoney float Если размер входных данных равен нулю, при объединении четырех частей числовых элементов возникает округление. DECLARE @n NUMERIC(38, 0)= 41538374868278625639929991208632320 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CONVERT(BINARY(8), @f) 0x4720000000000000 0x4720000000000001
numeric, money и smallmoney float Если входной масштаб ненулевой, при делении на 10^scale возникает округление. DECLARE @n NUMERIC(18, 10) = 12345678.0123456781 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CAST(@f AS BINARY(8)) 0x41678C29C06522C4 0x41678C29C06522C3
real или float numeric Улучшена точность округления в некоторых случаях. DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1)) 0,2 0,1
real или float numeric Улучшенная точность при округлениях до более чем 16 цифр в некоторых случаях. DECLARE @v decimal(38, 18) = 1E-18 SELECT @v 0.000000000000000000 0.000000000000000001
real или float money или smallmoney Улучшена точность при преобразовании большого числа в некоторых случаях. DECLARE @f float = 2SET @f = POWER(@f, 49) + POWER(@f, -2) SELECT CAST(@f AS money) 562949953421312.2048 562949953421312.25
(n)(var)char numeric Входные данные более 39 символов больше не обязательно активируют арифметический переполнение. DECLARE @value nchar(100) = '1.11111111111111111111111111111111111111' SELECT CAST(@value AS decimal(2,1)) Переполнение при арифметической операции 1,1
(n)(var)char bit Поддерживает ведущие пробелы и знаки. DECLARE @value nvarchar(100) = '1' SELECT CAST(@value AS bit) Сбой nvarchar преобразования при преобразовании значения "1" в бит типа данных. 1
datetime time или datetime2 Улучшенная точность при преобразовании в типы даты и времени с более высокой точностью. Помните, что значения даты и времени хранятся в виде галок, представляющих 1/300 секунду. Новые типы времени и datetime2 хранят дискретное число цифр, где число цифр соответствует точности. DECLARE @value datetime = '1900-01-01 00:00:00.003' SELECT CAST(@value AS time(7)) 00:00:00.0030000 00:00:00.0033333
time или datetime2 datetime Улучшено округление в некоторых случаях. DECLARE @value time(4) = '00:00:00.0045' SELECT CAST(@value AS datetime) 1900-01-01 00:00:00.007 1900-01-01 00:00:00.003

Операция

Операция Изменение Пример запроса Результат для уровня <совместимости 130 Результат для уровня совместимости 130
Используйте или DEGREES встроенную RADIANS функцию, которая использует числовый тип данных. DEGREES делится на pi/180, где он ранее умножен на 180/pi. RADIANSАналогично. DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1) 57.295779513082323000 57.295779513082322865
Числовое добавление или вычитание, если масштаб одного операнда превышает масштаб результата. Округление всегда происходит после добавления или вычитания, в то время как ранее это может произойти раньше. DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2 8.8 8,9
CONVERT с NULL стилем. CONVERT при использовании NULL стиля всегда возвращается NULL , когда целевой тип является числовым. SELECT CONVERT (SMALLINT, '0', NULL); 0 NULL
DATEPART использует параметр microseconds или nanoseconds с типом данных datetime. Значение больше не усечено на уровне миллисекунда перед преобразованием в микро- или наносекунды. DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt); 3000 3333
DATEDIFF использует параметр microseconds или nanoseconds с типом данных datetime. Значение больше не усечено на уровне миллисекунда перед преобразованием в микро- или наносекунды. DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME = '1900-01-01 00:00:00.007' SELECT DATEDIFF(MICROSECOND, @d1, @d2) 3000 3333
Сравнение значений datetime и datetime2 с ненулевыми значениями для миллисекунд. Значение datetime больше не усечено на уровне миллисекунда при выполнении сравнения со значением datetime2. Это означает, что определенные значения, которые ранее сравнивались равными, больше не сравниваются. DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME2(3) = @d1 SELECT CAST(@d1 AS datetime2(7)), @d2SELECT CASE WHEN (@d1=@d2) THEN 'equal' ELSE 'unequal' END 1900-01-01 00:00:00.003000, 1900-01-01 00:00:00.003 равно 1900-01-01 00:00:00.003333, 1900-01-01 00:00:00,003 неравный
ROUND функция, использующая float тип данных. Округление результатов отличается. SELECT ROUND(CAST (-0.4175 AS FLOAT), 3) -0.418 -0.417

Приложение B. Действия по проверке и обновлению сохраненных структур

Мы рекомендуем определить, имеет ли база данных все сохраненные структуры, затронутые изменениями на уровне совместимости 130, и перестроить все затронутые структуры.

Это относится только к сохраненным структурам, созданным в базе данных в более старой версии SQL Server или на уровне совместимости, который ниже 130. К сохраненным структурам, которые потенциально затронуты, относятся следующие:

  • Табличные данные, подверженные ограничениям CHECK
  • Сохраненные вычисляемые столбцы
  • Индексы, использующие вычисляемые столбцы в ключе или включенные столбцы
  • Отфильтрованные индексы
  • Индексированные представления

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

Шаг 1. Проверка уровня совместимости базы данных

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

Шаг 2. Определение затронутых сохраненных структур

Определите, содержит ли база данных все сохраненные структуры, затронутые улучшенной точностью и логикой преобразования на уровне совместимости 130, в любом из следующих способов:

  • DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, который проверяет все структуры в базе данных.
  • DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, который проверяет структуры, связанные с одной таблицей.

Этот параметр WITH EXTENDED_LOGICAL_CHECKS необходим, чтобы убедиться, что сохраненные значения сравниваются с вычисляемых значений, а также помечают случаи, в которых есть разница. Так как эти проверки являются обширными, среда выполнения инструкций DBCC , использующих этот параметр, больше, чем выполняемые DBCC операторы без параметра. Поэтому рекомендация для больших баз данных заключается в том, чтобы DBCC CHECKTABLE определить отдельные таблицы.

DBCC CHECKCONSTRAINTS можно использовать для проверки CHECK ограничений. Эту инструкцию можно использовать на уровне базы данных или таблицы.

DBCC CHECK операторы всегда должны выполняться во время периода обслуживания из-за потенциального влияния проверок на рабочую нагрузку в сети.

Проверка на уровне базы данных

Проверка на уровне базы данных подходит для небольших и умеренных баз данных. Используйте проверку на уровне таблицы для больших баз данных.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS используется для проверки всех сохраненных структур в базе данных.

DBCC CHECKCONSTRAINTS используется для проверки всех CHECK ограничений в базе данных.

DBCC CHECKCONSTRAINTS используется для проверки целостности ограничений. Используйте следующий скрипт для проверки базы данных:

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

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

CHECKCONSTRAINTS Если инструкция завершена и не возвращает набор результатов, никаких дополнительных действий не требуется.

Если инструкция возвращает набор результатов, каждая строка в результатах указывает на нарушение ограничения, а также включает значения, которые нарушают ограничение.

  • Сохраните имена таблиц и ограничений вместе со значениями, которые вызвали нарушение ( WHERE столбец в наборе результатов).

В следующем примере показана таблица с CHECK ограничением и одна строка, которая удовлетворяет ограничению на более низких уровнях совместимости, но нарушает ограничение на уровне совместимости 130.

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
    c2 datetime,
    c3 datetime,
    c4 int,
    CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2,c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(
    convert(datetime, '1900-01-01 00:00:00.997'),
    convert(datetime, '1900-01-01 00:00:01'), 3
)
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

Команда CHECKCONSTRAINT возвращает следующие результаты.

Таблица Ограничение Где
[dbo]. [table1] [chk1] [c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 01 00:00:01.000' AND [c4] = '3'

Этот результат указывает, что ограничение [chk1] нарушается для сочетания значений столбцов в параметре Where.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS проверяет все сохраненные структуры в базе данных. Это самый удобный вариант, так как одна инструкция проверяет все структуры в базе данных. Однако этот параметр не подходит для больших баз данных из-за ожидаемого времени выполнения инструкции.

Используйте следующий скрипт для проверки всей базы данных:

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS
GO
DBCC TRACEOFF(139, -1)
GO

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

CHECKDB Если инструкция выполнена успешно, никаких дополнительных действий не требуется.

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

  1. Сохраните результаты выполнения инструкции DBCC , найденной в области сообщений в SQL Server Management Studio (SSMS), в файл.
  2. Убедитесь, что любая из обнаруженных ошибок связана с сохраненными структурами

Таблица 1. Сохраненные структуры и соответствующие сообщения об ошибках для несоответствий

Затронутый тип структуры Наблюдаемые сообщения об ошибках Запишите
Сохраненные вычисляемые столбцы Msg 2537, ошибка таблицы уровня 16: идентификатор объекта object_id>, идентификатор <<индекса index_id> , . Сбой проверки записи (допустимый вычисляемый столбец). Значения: . Идентификатор объекта object_id и идентификатор <<индекса index_id>>
Индексы, ссылающиеся на вычисляемые столбцы в ключе или включенные столбцы Отфильтрованные индексы Ошибка таблицы Msg 8951: таблица "<table_name>" (идентификатор <object_id>). Строка данных не содержит соответствующую строку индекса в индексе "<index_name>" (id index_id>) And/or Msg 8952 Table error: table "<table_name>" (идентификатор <<table_name>). Строка индекса в индексе '' (идентификатор <index_id>) не соответствует ни одной строке данных. Кроме того, могут быть вторичные ошибки 8955 и/или 8956. Это содержит сведения о точных строках, затронутых. Это упражнение может быть проигнорировано. Идентификатор объекта object_id и идентификатор <<индекса index_id>>
Индексированные представления Msg 8908 Индексированного представления "<view_name>" (идентификатор <объекта object_id>) не содержит всех строк, создаваемых определением представления. И /или Msg 8907 Индексированного представления "<view_name>" (идентификатор <объекта object_id>) содержит строки, которые не были созданы определением представления. Идентификатор <объекта object_id>

После завершения проверки уровня базы данных перейдите к шагу 3.

Проверка на уровне объекта

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

Используйте запросы в разделе "Приложение C" , чтобы определить потенциально затронутые таблицы. Скрипт в разделе "Приложение D " можно использовать для создания CHECKTABLE и CHECKCONSTRAINTS ограничений на основе запросов, перечисленных в разделе "Приложение C ".

DBCC CHECKCONSTRAINTS

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

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

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

CHECKCONSTRAINTS Если инструкция завершена и не возвращает набор результатов, никаких дополнительных действий не требуется.

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

Сохраните имена таблиц и ограничений вместе со значениями, которые вызвали нарушение ( WHERE столбец в наборе результатов).

DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS

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

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

CHECKTABLE Если инструкция выполнена успешно, никаких дополнительных действий не требуется.

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

  1. Сохраните результаты выполнения инструкции DBCC , найденной в области сообщений в SSMS, в файл.
  2. Убедитесь, что любая из обнаруженных ошибок связана с сохраненными структурами, как указано в таблице 1.
  3. После завершения проверки уровня таблицы перейдите к шагу 3.

Шаг 3. Обновление до уровня совместимости 130

Если уровень совместимости базы данных уже равен 130, этот шаг можно пропустить.

Уровень совместимости базы данных можно изменить на 130 с помощью следующего сценария:

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Примечание.

Так как оптимизатор запросов изменяется на уровне совместимости 130, рекомендуется включить хранилище запросов перед изменением уровня совместимости. Дополнительные сведения см. в разделе "Сохранение стабильности производительности во время обновления до более новой версии SQL Server" в хранилище запросов сценариях использования.

Шаг 4. Обновление сохраненных структур

Если во время проверки, выполняемой на шаге 2, не обнаружены несоответствия, вы выполнили обновление и можете пропустить этот шаг. Если несоответствия обнаружены на шаге 2, для удаления несоответствий из базы данных требуются дополнительные действия. Необходимые действия зависят от типа затронутой структуры.

Внимание

Выполните действия по восстановлению на этом шаге только после изменения уровня совместимости базы данных на 130.

Резервное копирование базы данных (или баз данных)

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

Ограничения CHECK

Исправление CHECK нарушений ограничений требует изменения данных в таблице или CHECK самого ограничения.

В имени ограничения (полученного на шаге 2) можно получить определение ограничения следующим образом:

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Чтобы проверить затронутые строки таблицы, можно использовать сведения о том, где ранее возвращались инструкцией DBCC CHECKCONSTRAINTS :

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

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

Обновление данных таблицы

Нет жесткого правила, указывающее, как следует обновлять данные. Как правило, для каждой разной инструкции Where, возвращаемой оператором DBCC CHECKCONSTRAINTS, будет выполняться следующая инструкция обновления:

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Рассмотрим следующую таблицу с ограничением и строкой, которая нарушает ограничение на уровне совместимости 130:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
c2 datetime,
c3 datetime,
c4 int,
CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2, c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(convert(datetime, '1900-01-01 00:00:00.997'),
convert(datetime, '1900-01-01 00:00:01'), 3)
GO

В этом примере ограничение является простым. Столбец c4 должен быть равен выражению, в котором участвует c2 и c3. Чтобы обновить таблицу, присвойте этому значению c4следующее:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130
GO
UPDATE dbo.table1 SET c4 = datediff (ms, c2,c3)
WHERE [c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' AND [c4] = '3'
GO

Обратите внимание, что предложение WHERE , используемое в инструкции обновления, соответствует сведениям Where, возвращаемым DBCC CHECKCONSTRAINTS.

Обновление ограничения CHECK

Чтобы изменить CHECK ограничение, необходимо удалить и повторно создать его. Рекомендуется выполнять оба варианта в одной транзакции, только если возникают проблемы с обновленным определением ограничения. Вы можете использовать следующее Transact-SQL:

BEGIN TRANSACTION

ALTER TABLE [schema_name].[table_name]

DROP CONSTRAINT [constraint_name]

ALTER TABLE [schema_name].[table_name]

ADD CONSTRAINT [constraint_name]

CHECK (new_constraint_definition)

COMMIT

GO

The following example updates the constraint chk1 in dbo.table1:

BEGIN TRANSACTION

ALTER TABLE dbo.table1

DROP CONSTRAINT chk1

ALTER TABLE dbo.table1

ADD CONSTRAINT chk1

CHECK (c4 <= DATEDIFF (ms, c2, c3))

COMMIT

GO

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

Самый простой способ обновить сохраненные вычисляемые столбцы — обновить один из столбцов, на которые ссылается вычисляемый столбец. Новое значение столбца может совпадать со старым значением, таким образом, что операция не изменяет данные пользователя.

Выполните эти действия для каждого object_id из связанных с несоответствиями в вычисляемых столбцах, которые вы указали на шаге 2.

  1. Определите вычисляемые столбцы:

    • Выполните следующий запрос, чтобы получить имя таблицы и имена сохраненных вычисляемых столбцов для отмеченного object_id:

      SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS 'table',
QUOTENAME(c1.name) AS 'persisted computed column',
c1.column_id AS 'computed_column_id' ,
definition AS 'computed_column_definition'
FROM sys.tables t
JOIN sys.computed_columns c1 ON t.object_id=c1.object_id
AND c1.is_persisted=1
JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.object_id=object_id

  2. Определите ссылочные столбцы:

  • Выполните следующий запрос, чтобы определить столбцы, на которые ссылается вычисляемый столбец. Запишите одно из имен столбцов, на которые ссылается ссылка:

    SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) AS 'referencing object',
o.type_desc AS 'object type', referenced_minor_id AS 'referenced_column_id', c.name AS 'referenced_column_name'
FROM sys.sql_expression_dependencies sed
JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id
JOIN sys.objects o ON sed.referencing_id=o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
JOIN sys.columns c ON o.object_id=c.object_id AND sed.referenced_minor_id=c.column_id
WHERE referencing_class=1 AND referenced_class=1 AND referencing_id=object_id AND referencing_minor_id=computed_column_id

  1. Запустите инструкцию UPDATE , включающую один из ссылочных столбцов, чтобы активировать обновление вычисляемого столбца:

    • Следующая инструкция активирует обновление столбца, на который ссылается вычисляемый столбец, а также активирует обновление вычисляемого столбца.

      UPDATE [schema_name].[table_name]
SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)

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

    • Помните, что для очень больших таблиц может не потребоваться обновить все строки в одной транзакции. В таком случае можно запустить обновление в пакетах, добавив WHERE предложение в инструкцию обновления, которая определяет диапазон строк; например, на основе первичного ключа.

  2. Определите индексы, ссылающиеся на вычисляемый столбец.

    SELECT i.name AS [index name]
FROM sys.index_columns ic JOIN sys.indexes i ON ic.object_id=i.object_id AND ic.index_id=i.index_id
WHERE i.object_id=object_id AND ic.column_id=computed_column_id

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

Индексы, отфильтрованные индексы и индексированные представления

Несоответствия в индексах соответствуют ошибкам 8951 и 8952 (для таблиц) или 8907 и 8908 (для представлений) в DBCC CHECK выходных данных шага 2.

Чтобы устранить эти несоответствия, выполните команду DBCC CHECKTABLE с REPAIR_REBUILDпомощью . Это приведет к восстановлению структур индексов без потери данных. Однако база данных должна находиться в однопользовательском режиме и поэтому недоступна другим пользователям во время восстановления.

Кроме того, можно вручную перестроить затронутые индексы. Этот параметр следует использовать, если рабочая нагрузка не может быть отключена, так как перестроение индекса можно выполнить как операцию ONLINE (в поддерживаемых выпусках SQL Server).

Перестроение индексов

Если параметр базы данных в однопользовательском режиме не является параметром, можно отдельно перестроить индексы с помощью ALTER INDEX REBUILDкаждого индекса, определенного на шаге 2.

Используйте следующий запрос, чтобы получить имена таблиц и индексов для заданного object_id и index_id.

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'table', i.name AS 'index_name'

FROM sys.objects o JOIN sys.indexes i ON o.object_id=i.object_id

WHERE o.object_id = object_id AND i.index_id = index_id

Используйте следующую инструкцию для перестроения индекса:

ALTER INDEX index_name ON [schema_name].[table_name] REBUILD WITH (ONLINE=ON)

Примечание.

Если вы используете выпуски Standard, Web или Express, сборка индекса в Интернете не поддерживается. Поэтому параметр WITH (ONLINE=ON) должен быть удален из инструкции ALTER INDEX .

В следующем примере показано перестроение отфильтрованного индекса:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table2
(
    c2 datetime,
    c3 float
)
GO
INSERT dbo.table2 (c2,c3) VALUES ('1899-12-31 23:58:00.470', -0.00138344907407406)
GO
CREATE INDEX ix_1 ON dbo.table2(c2)
WHERE (c2=-0.00138344907407406)
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130GOALTER INDEX ix_1 ON [dbo].[table2] REBUILD WITH (ONLINE=ON)
GO

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

Восстановление с помощью DBCC

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

USE [database_name]

GO

ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKTABLE (object_id, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

ALTER DATABASE CURRENT SET MULTI_USER

GO

Приложение C. Запросы для идентификации таблиц кандидатов

Следующие сценарии определяют таблицы кандидатов, которые могут потребоваться проверить с помощью, DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKSисходя из наличия сохраненных структур и ограничений, использующих типы данных, затронутые улучшениями уровня совместимости 130.

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

Индексированные представления

Следующий запрос возвращает все индексированные представления, ссылающиеся на столбцы с помощью затронутых типов данных, или с помощью любой из затронутых встроенных функций:

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'view', QUOTENAME(i.name) AS 'index',QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'referenced table', QUOTENAME(c.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the view definition contains a float or datetime value, or a conversion to such value

s.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.objects o ON sed.referencing_id = o.object_id AND o.type=N'V'

JOIN sys.indexes i ON o.object_id=i.object_id

JOIN sys.sql_modules s ON s.object_id=o.object_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_idJOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=1 AND referenced_class=1 AND (c.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

) OR s.[definition] LIKE '%DATEDIFF%'

OR s.[definition] LIKE '%CONVERT%'

OR s.[definition] LIKE '%CAST%'

OR s.[definition] LIKE '%DATEPART%'

OR s.[definition] LIKE '%DEGREES%')

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

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

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with computed column',

QUOTENAME(c1.name) AS 'computed column', c1.is_persisted,QUOTENAME(c2.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the column definition contains a float or datetime value, or a conversion to such value

c1.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id

JOIN sys.columns c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id=c2.column_id

JOIN sys.types t ON c2.system_type_id=t.system_type_idWHERE referencing_class=1 AND referenced_class=1

AND (c2.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

) OR c1.[definition] LIKE '%DATEDIFF%'

OR c1.[definition] LIKE '%CONVERT%'

OR c1.[definition] LIKE '%DATEPART%'

OR c1.[definition] LIKE '%DEGREES%')

AND (

-- the column is persisted

c1.is_persisted=1

-- OR the column is included in an index

OR EXISTS (SELECT 1 FROM sys.index_columns ic WHERE ic.object_id=c1.object_id AND ic.column_id=c1.column_id)

)

Отфильтрованные индексы

Следующий запрос возвращает все таблицы с отфильтрованными индексами, ссылающимися на столбцы в условии фильтра, которые повлияли на типы данных:

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with filtered index',

QUOTENAME(i.name) AS 'referencing index',

QUOTENAME(c.name) AS 'referenced column',

t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is where the filter condition contains a float or datetime value

i.filter_definition AS 'filter condition'

FROM sys.sql_expression_dependencies sed

JOIN sys.indexes i ON sed.referencing_id=i.object_id AND sed.referencing_minor_id=i.index_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_id

JOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=7 AND referenced_class=1 AND i.has_filter=1

AND c.system_type_id IN ( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

)

Проверочные ограничения

В следующем запросе перечислены все таблицы с ограничениями проверки, ссылающимися на затронутые типы данных или встроенные функции:

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with check constraint',

QUOTENAME(c.name) AS 'constraint_name', c.definition AS 'constraint_definition',

QUOTENAME(col.name) AS 'referenced column', t.name AS 'data type'

FROM sys.sql_expression_dependencies sed

JOIN sys.check_constraints c ON sed.referencing_id=c.object_id AND sed.referencing_class=1

JOIN sys.columns col ON sed.referenced_id=col.object_id AND sed.referenced_minor_id=col.column_id

JOIN sys.types t ON col.system_type_id=t.system_type_id

WHERE referencing_class=1 AND referenced_class=1 AND (col.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint)

OR c.[definition] LIKE '%DATEDIFF%'

OR c.[definition] LIKE '%CONVERT%'

OR c.[definition] LIKE '%DATEPART%'

OR c.[definition] LIKE '%DEGREES%')

Приложение D. Скрипт для создания инструкций CHECK*

Следующий скрипт объединяет запросы из предыдущего приложения и упрощает результаты путем представления списка таблиц и представлений в виде CHECKCONSTRAINTS и CHECKTABLE инструкций.

DECLARE @CRLF nvarchar(10) = CHAR(13) + CHAR(10);
DECLARE @sql nvarchar(max) = N'DBCC TRACEON(139,-1); ' + @CRLF ;

SELECT @sql += N'DBCC CHECKTABLE (N''' + object_for_checktable + N''') WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS; ' + @CRLF
FROM
(

--indexed views
SELECT DISTINCT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed
 INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id AND o.type = N'V'
 INNER JOIN sys.indexes AS i ON o.object_id = i.object_id
 INNER JOIN sys.sql_modules AS s ON s.object_id = o.object_id
 INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id
 INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id

WHERE referencing_class = 1 AND referenced_class=1 
 AND (c.system_type_id IN 
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR s.[definition] LIKE N'%DATEDIFF%'
 OR s.[definition] LIKE N'%CONVERT%'
 OR s.[definition] LIKE N'%CAST%'
 OR s.[definition] LIKE N'%DATEPART%'
 OR s.[definition] LIKE N'%DEGREES%')

UNION

--persisted computed columns
SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.computed_columns AS c1 ON sed.referencing_id = c1.object_id AND sed.referencing_minor_id = c1.column_id
INNER JOIN sys.columns AS c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id = c2.column_id
INNER JOIN sys.types AS t ON c2.system_type_id = t.system_type_id
WHERE referencing_class = 1 AND referenced_class = 1 
 AND (c2.system_type_id IN
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR c1.[definition] LIKE N'%DATEDIFF%'
 OR c1.[definition] LIKE N'%CONVERT%'
 OR c1.[definition] LIKE N'%DATEPART%'
 OR c1.[definition] LIKE N'%DEGREES%')
AND (
-- the column is persisted
c1.is_persisted = 1 
-- OR the column is included in an index
OR EXISTS (SELECT 1 FROM sys.index_columns AS ic 
WHERE ic.object_id = c1.object_id AND ic.column_id=c1.column_id)
)

UNION

--indexed views
SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.indexes AS i ON sed.referencing_id = i.object_id AND sed.referencing_minor_id = i.index_id
INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id 
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE referencing_class = 7 AND referenced_class = 1 AND i.has_filter = 1
AND c.system_type_id IN ( 
 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
)) AS a

SELECT @sql += N'DBCC CHECKCONSTRAINTS (N''' + object_for_checkconstraints + N'''); ' + @CRLF
FROM
(

SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checkconstraints'
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.check_constraints AS c ON sed.referencing_id = c.object_id AND sed.referencing_class = 1
INNER JOIN sys.columns AS col ON sed.referenced_id = col.object_id AND sed.referenced_minor_id = col.column_id
INNER JOIN sys.types AS t ON col.system_type_id = t.system_type_id
WHERE referencing_class = 1 AND referenced_class = 1 AND (col.system_type_id IN 
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR c.[definition] LIKE N'%DATEDIFF%'
 OR c.[definition] LIKE N'%CONVERT%'
 OR c.[definition] LIKE N'%DATEPART%'
 OR c.[definition] LIKE N'%DEGREES%')
) a

SET @sql += N'DBCC TRACEOFF(139,-1);';

PRINT @sql;

--to run the script immediately, use the following command:
--EXECUTE sp_executesql @sql;
GO