Уровень совместимости инструкции ALTER DATABASE (Transact-SQL)

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Определяет поведение обработки запросов и Transact-SQL для обеспечения совместимости с указанной версией ядра SQL. См. дополнительные сведения о других параметрах ALTER DATABASE.

Дополнительные сведения о соглашениях о синтаксисе см. в статье Соглашения о синтаксисе в Transact-SQL.

Синтаксис

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 }

Аргументы

database_name

Имя изменяемой базы данных.

COMPATIBILITY_LEVEL { 160 | 150 | 140 | 130 | 120 | 110 | 100 | 90 | 80 }

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

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

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

Замечания

Для всех установок SQL Server уровень совместимости по умолчанию связан с версией ядро СУБД. Новые базы данных устанавливаются на этот уровень, если model база данных не имеет более низкого уровня совместимости. Для баз данных, подключаемых или обновляемых с любой более ранней версии SQL Server, сохраняется существующий уровень совместимости, если он не ниже минимального, допустимого для этого экземпляра SQL Server. При перемещении базы данных с уровнем совместимости ниже заданного ядром СУБД уровня, автоматически устанавливается минимальный допустимый уровень совместимости. Это относится и к системным, и к пользовательским базам данных.

Для SQL Server 2017 (14.x) ожидается следующее поведение при присоединении или восстановлении базы данных и после обновления на месте:

• Если уровень совместимости пользовательской базы данных до обновления был 100 или выше, после обновления он останется таким же.
• Если уровень совместимости пользовательской базы данных был 90 до обновления, в обновленной базе данных устанавливается уровень совместимости 100, который является самым низким поддерживаемым уровнем совместимости в SQL Server 2017 (14.x).
• Уровни tempdbсовместимости баз данных , modelmsdbи ресурсов задаются на уровне совместимости по умолчанию для заданной версии ядро СУБД.
• Системная база данных master сохраняет уровень совместимости, который она имела до обновления. Это не повлияет на поведение пользовательской базы данных.

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

Измените уровень совместимости базы данных с помощью инструкции ALTER DATABASE. Новый параметр уровня совместимости для базы данных вступит в силу после выдачи USE <database> или обработки нового имени входа в этой базе данных в качестве контекста базы данных по умолчанию.

Чтобы просмотреть текущий уровень совместимости базы данных, запросите столбец compatibility_level представления каталога sys.databases.

База данных распространителя, созданная в более ранней версии SQL Server и обновлена до SQL Server 2016 (13.x) RTM или пакета обновления 1, имеет уровень совместимости 90, который не поддерживается для других баз данных. Это не влияет на функциональные возможности репликации. Обновление до более поздних пакетов обновления и версий SQL Server приведет к увеличению уровня совместимости базы данных распространителя, чтобы она соответствовала master базе данных.

Если в целом для базы данных требуется уровень совместимости 120 или выше, но используете модель оценки кратности SQL Server 2012 (11.x), соответствующую уровню совместимости базы данных 110, см. ALTER DATABASE SCOPED CONFIGURATION и в частности, ключевое слово LEGACY_CARDINALITY_ESTIMATION = ON.

Примечания для SQL Azure

Уровень совместимости по умолчанию — SQL Server 2022 (160) для вновь созданных баз данных в База данных SQL Azure.

Уровень совместимости по умолчанию — SQL Server 2019 (150) для вновь созданных баз данных в Управляемый экземпляр SQL Azure.

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

Корпорация Майкрософт настоятельно рекомендует клиентам выполнять обновление до последнего уровня совместимости, чтобы использовать последние улучшения оптимизации запросов. Советы по оценке различий производительности наиболее важных запросов между двумя разными уровнями совместимости в База данных SQL Azure см. в разделе "Улучшенная производительность запросов с уровнем совместимости 130" в База данных SQL Azure. В этой статье описывается уровень совместимости 130 и SQL Server, но при обновлении до уровня 140 или выше для SQL Server и базы данных SQL Azure применяется тот же способ.

Не все функции, которые зависят от уровня совместимости, поддерживаются в База данных SQL Azure.

Поиск текущего уровня совместимости

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

SELECT name, compatibility_level FROM sys.databases;

Чтобы определить версию подключенного ядро СУБД, выполните следующий запрос.

SELECT SERVERPROPERTY('ProductVersion');

Уровни совместимости и обновления ядра СУБД

Уровень совместимости базы данных — это полезное средство для модернизации базы данных. Оно позволяет обновлять ядро СУБД SQL Server, сохраняя функциональное состояние подключенных приложений, не изменяя уровень совместимости до обновления базы данных. Это означает, что можно обновить более раннюю версию SQL Server (например, SQL Server 2008 (10.0.x)) до SQL Server или База данных SQL Azure (включая Управляемый экземпляр SQL Azure) без изменений приложения (за исключением подключения к базе данных). Дополнительные сведения см. в статье Сертификация на совместимость.

Если приложению не требуются улучшения, доступные только на более высоком уровне совместимости базы данных, допускается обновление ядра СУБД SQL Server, сохраняя прежний уровень совместимости. Дополнительные сведения об использовании уровня совместимости для обеспечения обратной совместимости см. в статье Сертификация на совместимость.

Уровни совместимости и хранимые процедуры

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

Использование уровня совместимости для обратной совместимости

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

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

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

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

• Прекращенная функциональность, представленная в данной версии SQL Server, не защищена уровнем совместимости. Это относится к функциям, которые были удалены из ядро СУБД SQL Server. Например, FASTFIRSTROW указание было прекращено в SQL Server 2012 (11.x) и заменено указанием OPTION (FAST n ) . Установка уровня совместимости базы данных на 110 не приведет к восстановлению прекращенного указания. Дополнительные сведения о прекращенных функциях см. в статье Нерекомендуемые функции ядра СУБД в SQL Server.

• Критические изменения , внесенные в определенную версию SQL Server, могут не быть защищены уровнем совместимости. Это относится к изменениям поведения между версиями SQL Server ядро СУБД. Поведение Transact-SQL обычно зависит от уровня совместимости. Однако измененные или удаленные системные объекты не защищены уровнем совместимости.

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

  Примеры критических изменений, не защищенных уровнем совместимости.

  • Изменение имен столбцов в системных объектах. В SQL Server 2012 (11.x) столбец single_pages_kb sys.dm_os_sys_info был переименован pages_kbв . Независимо от уровня совместимости запрос SELECT single_pages_kb FROM sys.dm_os_sys_info вызывает ошибку 207 (Недопустимое имя столбца).
  • Удаление системных объектов. В SQL Server 2012 (11.x) sp_dboption удалено. Независимо от уровня совместимости инструкция EXEC sp_dboption 'AdventureWorks2022', 'autoshrink', 'FALSE'; создаст ошибку 2812 (Couldn't find stored procedure 'sp_dboption').

  Подробные сведения о критических изменениях см. в статьях Критические изменения в ядре СУБД в SQL Server 2019 (15.x), Критические изменения в функциях ядра СУБД в SQL Server 2017 (14.x), Критические изменения в функциях ядра СУБД в SQL Server 2016 и Критические изменения в функциях ядра СУБД в SQL Server 2014.

Различия между уровнями совместимости

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

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

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

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

Основные изменения, влияющие на план, добавляются только на уровень совместимости по умолчанию новой версии ядро СУБД:

1. Исправления оптимизатора запросов, выпущенные для предыдущих версий SQL Server под флагом трассировки 4199, автоматически включены на уровне совместимости по умолчанию новой версии SQL Server.

   Область применения: SQL Server (начиная с версии SQL Server 2016 (13.x)), База данных SQL Azure.

   Например, при выпуске SQL Server 2016 (13.x) все исправления оптимизатора запросов, выпущенные для предыдущих версий SQL Server (и соответствующие уровни совместимости 100–120) автоматически включены для баз данных, использующих уровень совместимости ПО умолчанию SQL Server 2016 (13.x). Необходимо явно включить только исправления оптимизатора запросов после выпуска RTM.

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

   • На уровне сервера — с помощью флага трассировки 4199.
   • На уровне базы данных — с помощью параметра QUERY_OPTIMIZER_HOTFIXES в ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
   • На уровне запроса — с указанием запроса USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES' путем изменения запроса.
   • На уровне запроса — с USE HINT 'ENABLE_QUERY_OPTIMIZER_HOTFIXES' без изменения кода, с помощью функции Указание хранилища запросов (предварительная версия).

   Позже при выпуске SQL Server 2017 (14.x) все исправления оптимизатора запросов, выпущенные после того, как RTM SQL Server 2016 (13.x) автоматически включено для баз данных с помощью уровня совместимости по умолчанию SQL Server 2017 (14.x). Это накопительное поведение, которое включает также исправления всех предыдущих версий. Необходимо также явно включить только исправления оптимизатора запросов после выпуска RTM.

   В следующей таблице описывается это поведение:

   Версия ядра СУБД	Уровень совместимости базы данных	TF 4199	Изменения в оптимизаторе запросов из всех предыдущих уровней совместимости базы данных	Изменения в оптимизаторе запросов для версии ядра СУБД после RTM
   13 (SQL Server 2016 (13.x))	100–120 130	Выключено С Выкл. Включено	Отключен Включен Включено Активировано	Выключено Активировано Выключено Включен
   14 (SQL Server 2017 (14.x))	100–120 130 140	Выключено С Выкл. С Выкл. Включено	Отключен Включен Включено Включен Включено Активировано	Выключено Активировано Выключено Активировано Выключено Включен
   15 (SQL Server 2019 (15.x)) и 12 (База данных SQL Azure)	100–120 130–140 150	Выключено С Выкл. С Выкл. Включено	Отключен Включен Включено Включен Включено Активировано	Выключено Активировано Выключено Активировано Выключено Включен
   16 (SQL Server 2022 (16.x)) и 12 (База данных SQL Azure)	100–120 От 130 до 150 160	Выключено С Выкл. С Выкл. Включено	Отключен Включен Включено Включен Включено Активировано	Выключено Активировано Выключено Активировано Выключено Включен

   Оптимизатор запросов исправляет ошибки, связанные с неправильными результатами или ошибками нарушения доступа, не защищены флагом трассировки 4199. Эти исправления не считаются необязательными.

2. Изменения оценки кратности, выпущенные в SQL Server, и База данных SQL Azure включены только на уровне совместимости по умолчанию новой версии ядро СУБД, но не на предыдущих уровнях совместимости.

   Например, при выпуске SQL Server 2016 (13.x) изменения в процессе оценки кратности были доступны только для баз данных с использованием уровня совместимости по умолчанию SQL Server 2016 (13.x). Предыдущие уровни совместимости сохранили поведение оценки кратности, доступное до SQL Server 2016 (13.x).

   Позже, когда sql Server 2017 (14.x) был выпущен, новые изменения в процессе оценки кратности были доступны только для баз данных с использованием уровня совместимости по умолчанию SQL Server 2017 (14.x). Уровень совместимости базы данных 130 не изменяет поведение оценки кратности SQL Server 2016 (13.x).

   В следующей таблице описывается это поведение:

   Версия ядра СУБД	Уровень совместимости базы данных	Изменения оценки кратности в новой версии
   13 (SQL Server 2016 (13.x))	< 130 130	Выключено Включен
   14 (SQL Server 2017 (14.x))1	< 140 140	Выключено Включен
   15 (SQL Server 2019 (15.x))1	< 150 150	Выключено Включен
   16 (SQL Server 2022 (16.x))1	< 160 160	Выключено Включен

   ¹ Также применимо к База данных SQL Azure.

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

Различия между уровнями совместимости 150 и 160

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

Различия между уровнями совместимости 140 и 150

В этом разделе описываются новые возможности, обусловленные появлением уровня совместимости 150.

Дополнительные сведения о функциях обработки запросов, доступных на уровне 150 совместимости базы данных, см. в статьях Новые возможности в SQL Server 2019 и Интеллектуальная обработка запросов в базах данных SQL.

Различия между уровнями совместимости 130 и 140

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

Исправления, которые включались флагом трассировки 4199 в версиях SQL Server до SQL Server 2017, теперь включены по умолчанию. С режимом совместимости 140. Флаг трассировки 4199 по-прежнему можно использовать для новых исправлений оптимизатора запросов, выпущенных после SQL Server 2017. Дополнительные сведения о флаге трассировки 4199 см. в разделе Флаг трассировки 4199.

Различия между уровнями совместимости 120 и 130

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

Исправления, которые находились под флагом трассировки 4199 в более ранних версиях SQL Server до SQL Server 2016 (13.x), теперь включены по умолчанию. С режимом совместимости 130. Флаг трассировки 4199 по-прежнему применим для новых исправлений оптимизатора запросов, выпущенных после SQL Server 2016 (13.x). Чтобы использовать старый оптимизатор запросов в База данных SQL необходимо выбрать уровень совместимости 110. Дополнительные сведения о флаге трассировки 4199 см. в разделе Флаг трассировки 4199.

Различия между уровнем 120 и более низкими уровнями совместимости

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

Различия между более низкими уровнями совместимости и уровнями 100 и 110

В этом разделе описываются новые особенности поведения, обусловленные появлением уровня совместимости 110. Этот раздел также относится к уровням совместимости выше 110.

Различия между уровнями совместимости 90 и 100

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

Зарезервированные ключевые слова

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

На данном уровне совместимости зарезервированные ключевые слова включают в себя все ключевые слова, представленные на этом уровне или ниже. Таким образом, например, для приложений на уровне 110, все ключевые слова, перечисленные в предыдущей таблице, являются зарезервированными. На более низких уровнях совместимости ключевые слова уровня 100 остаются допустимыми именами объектов, но функции языка уровня 110, соответствующие этим ключевым словам, недоступны.

Будучи однажды представленным, ключевое слово остается зарезервированным. Например, зарезервированное ключевое слово PIVOT, которое было введено на уровне совместимости 90, является также зарезервированным на уровнях 100, 110 и 120.

Если приложение использует идентификатор, зарезервированный в качестве ключевого слова на его уровне совместимости, работа приложения приведет к ошибке. Чтобы обойти эту проблему, заключите идентификатор в квадратные скобки ([]) или кавычки (""). Например, чтобы обновить приложение, использующее идентификатор EXTERNAL, до уровня совместимости 90, можно изменить идентификатор на [EXTERNAL] или "EXTERNAL".

См. дополнительные сведения о зарезервированных ключевых словах.

Разрешения

Необходимо разрешение ALTER на базу данных.

Примеры

А. Изменение уровня совместимости

В следующем примере уровень AdventureWorks2022 совместимости образца базы данных базы данных изменяется на 150, значение по умолчанию для SQL Server 2019 (15.x).

ALTER DATABASE AdventureWorks2022
SET COMPATIBILITY_LEVEL = 150;
GO

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

SELECT name, compatibility_level
FROM sys.databases
WHERE name = db_name();
GO

B. Не учитывайте инструкция SET LANGUAGE, за исключением выполнения при уровне совместимости 120 или выше

Следующий запрос не учитывает инструкцию SET LANGUAGE, за исключением выполнения на уровне совместимости 120 или выше.

SET DATEFORMAT dmy;
DECLARE @t2 date = '12/5/2011' ;
SET LANGUAGE dutch;
SELECT CONVERT(varchar(11), @t2, 106);
GO

Результаты, если уровень совместимости менее 120: 12 May 2011

Результаты, если уровень совместимости равен 120 или выше: 12 mei 2011

C. При уровне совместимости 110 или ниже рекурсивные ссылки в правой части предложения EXCEPT создают бесконечный цикл.

WITH cte AS
    (SELECT * FROM (VALUES (1),(2),(3)) v (a)),
r AS
    (SELECT a FROM cte
    UNION ALL
    (SELECT a FROM cte EXCEPT SELECT a FROM r)
)
SELECT a
FROM r;
GO

D. Разница между стилями 0 и 121.

Если уровень совместимости ниже 110, по умолчанию используется стиль для операций CAST и CONVERT над типами данных time и datetime2 — 121, кроме случая, когда любой из этих типов используется в выражении вычисляемого столбца. Для вычисляемых столбцов используемый по умолчанию стиль — 0.

При уровне совместимости 110 и выше стиль по умолчанию для операций CAST и CONVERT над типами данных time и datetime2 всегда имеет значение 121. Дополнительные сведения см. в разделе Различия между более низкими уровнями совместимости и уровнями 100 и 110.

Дополнительные сведения о стилях даты и времени см. в описании CAST и CONVERT.

DROP TABLE IF EXISTS t1;
GO

CREATE TABLE t1 (c1 time(7), c2 datetime2);
GO

INSERT t1 (c1,c2) VALUES (GETDATE(), GETDATE());
GO

SELECT CONVERT(nvarchar(16),c1,0) AS TimeStyle0
       ,CONVERT(nvarchar(16),c1,121)AS TimeStyle121
       ,CONVERT(nvarchar(32),c2,0) AS Datetime2Style0
       ,CONVERT(nvarchar(32),c2,121)AS Datetime2Style121
FROM t1;
GO

Возвращаются следующие результаты:

Е. Присваивание переменной — оператор UNION верхнего уровня.

В параметре уровня совместимости базы данных 90 в инструкции, содержащей оператор UNION верхнего уровня, допускается назначение переменных, но возвращается непредвиденный результат. Например, в следующих инструкциях локальной переменной @v присваивается значение столбца BusinessEntityID из объединения двух таблиц. Если инструкция SELECT возвращает более одного значения, переменной присваивается последнее возвращенное значение. В этом случае переменной правильно присваивается последнее значение, но происходит также возврат результирующего набора инструкции SELECT UNION.

ALTER DATABASE AdventureWorks2022
SET compatibility_level = 110;
GO
USE AdventureWorks2022;
GO
DECLARE @v int;
SELECT @v = BusinessEntityID FROM HumanResources.Employee
UNION ALL
SELECT @v = BusinessEntityID FROM HumanResources.EmployeeAddress;
SELECT @v;

В параметре уровня совместимости базы данных 100 и выше назначение переменных не допускается в инструкции, содержащей оператор UNION верхнего уровня. Возвращается ошибка 10734.

Чтобы устранить эту ошибку, перепишите запрос, как показано в следующем примере.

DECLARE @v int;
SELECT @v = BusinessEntityID FROM
    (SELECT BusinessEntityID FROM HumanResources.Employee
     UNION ALL
     SELECT BusinessEntityID FROM HumanResources.EmployeeAddress) AS Test;
SELECT @v;

Связанный контент

• Сохранение стабильности производительности во время обновления до более новой версии SQL Server
• Изменение уровня совместимости базы данных и использование хранилище запросов
• Сертификация совместимости
• ALTER DATABASE (Transact-SQL)
• Обновление баз данных с помощью помощника по настройке запросов
• CREATE DATABASE
• Просмотр или изменение уровня совместимости базы данных
• Указания хранилища запросов