SET ANSI_NULLS (Transact-SQL)

  • Статья

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics AnalyticsPlatform System (PDW)

Указывает поведение, соответствующее СТАНДАРТу ISO, операторов сравнения Equals (=) и Not Equal To (<>) при использовании со значениями NULL в SQL Server.

Примечание.

SET ANSI_NULLS OFF и параметр базы данных OFF ANSI_NULLS не рекомендуется. Начиная с SQL Server 2017 (14.x), ANSI_NULLS всегда имеет значение ON. Не следует использовать нерекомендуемые возможности в новых приложениях. Дополнительные сведения см. в статье об устаревших функциях ядро СУБД в SQL Server 2017.

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server, бессерверный пул SQL в Azure Synapse Analytics, Microsoft Fabric

SET ANSI_NULLS { ON | OFF }

Синтаксис для Системы платформы Azure Synapse Analytics и Analytics (PDW)

SET ANSI_NULLS ON

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Замечания

Если ANSI_NULLS имеет значение ON, инструкция SELECT, использующая WHERE column_name = NULL ноль строк, даже если в column_name есть значения NULL. Инструкция SELECT, использующая WHERE column_name <> NULL ноль строк, даже если в column_name имеются значения, отличные от NULL.

Если ANSI_NULLS имеет значение OFF, операторы сравнения equals (=) и Not Equal To (<>) не соответствуют стандарту ISO. Инструкция SELECT, использующая WHERE column_name = NULL строки с пустыми значениями в column_name. Инструкция SELECT, использующая WHERE column_name <> NULL возвращающие строки, имеющие значения, отличные от NULL в столбце. Кроме того, инструкция SELECT, использующая WHERE column_name <> XYZ_value возвращающие все строки, которые не XYZ_value и не имеют значения NULL.

Если параметр ANSI_NULLS равен ON, все сравнения со значением NULL возвращают значение UNKNOWN. Когда SET ANSI_NULLS равняется OFF, сравнение любых значений с NULL вернет TRUE только в том случае, если сравниваемое значение тоже NULL. Если параметр SET ANSI_NULLS не указан, применяется значение параметра ANSI_NULLS текущей базы данных. Дополнительные сведения о параметре базы данных ANSI_NULLS см. в разделе ALTER DATABASE (Transact-SQL).

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

Логическое выражение SET ANSI_NULLS ON SET ANSI_NULLS OFF
NULL = NULL НЕИЗВЕСТНО TRUE
1 = NULL НЕИЗВЕСТНО FALSE
NULL <> NULL НЕИЗВЕСТНО FALSE
1 <> NULL НЕИЗВЕСТНО TRUE
NULL > NULL НЕИЗВЕСТНО НЕИЗВЕСТНО
1 > NULL НЕИЗВЕСТНО НЕИЗВЕСТНО
NULL IS NULL TRUE TRUE
1 IS NULL FALSE FALSE
NULL IS NOT NULL FALSE FALSE
1 IS NOT NULL TRUE TRUE

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

Чтобы скрипт работал в соответствии с первоначальным замыслом, вне зависимости от параметра базы данных ANSI NULLS или настроек SET ANSI_NULLS, в сравнениях, которые могут содержать значения NULL, следует использовать выражения IS NULL и IS NOT NULL.

Параметр ANSI_NULLS должен иметь значение ON для выполнения распределенных запросов.

Также параметр ANSI_NULLS должен иметь значение ON при создании или изменении индексов вычисляемых столбцов или индексированных представлений. Если SET ANSI_NULLS равно OFF, то при работе с таблицами, содержащими индексы вычисляемых столбцов, а также при работе с индексированными представлениями инструкции CREATE, UPDATE, INSERT и DELETE завершатся неудачно. SQL Server возвращает ошибку, которая выводит список всех параметров SET, которые нарушают необходимые значения. Кроме того, при выполнении инструкции SELECT, если параметр SET ANSI_NULLS отключен, SQL Server игнорирует значения индекса в вычисляемых столбцах или представлениях и разрешает операцию выбора, как если бы в таблицах или представлениях не было таких индексов.

Примечание.

ANSI_NULLS является одним из семи параметров директивы SET, которые должны быть установлены определенным образом при работе с вычисляемыми столбцами или индексированными представлениями. Параметрам ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER и CONCAT_NULL_YIELDS_NULL также должно быть присвоено значение ON, а параметру NUMERIC_ROUNDABORT — значение OFF.

Драйвер ODBC собственного клиента SQL Server и поставщик OLE DB собственного клиента SQL Server для SQL Server автоматически устанавливает ANSI_NULLS значение ON при подключении. Этот параметр можно настроить в источниках данных ODBC, в атрибутах подключения ODBC или в свойствах подключения OLE DB, заданных в приложении перед подключением к экземпляру SQL Server. По умолчанию значение SET ANSI_NULLS равно OFF.

Если параметр ANSI_DEFAULTS установлен в значение ON, параметр ANSI_NULLS также включается.

Значение ANSI_NULLS определяется во время выполнения, а не во время синтаксического анализа.

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

DECLARE @ANSI_NULLS VARCHAR(3) = 'OFF';  
IF ( (32 & @@OPTIONS) = 32 ) SET @ANSI_NULLS = 'ON';  
SELECT @ANSI_NULLS AS ANSI_NULLS;

Разрешения

Необходимо быть членом роли public.

Примеры

В следующем примере операторы сравнения Equals (=) Not Equal To (<>) используются для сравнения со значениями в таблице, которые равны или не равны NULL. Этот пример также демонстрирует, что использование конструкции IS NULL не зависит от значения параметра SET ANSI_NULLS.

-- Create table t1 and insert values.  
CREATE TABLE dbo.t1 (a INT NULL);  
INSERT INTO dbo.t1 values (NULL),(0),(1);  
GO  
  
-- Print message and perform SELECT statements.  
PRINT 'Testing default setting';  
DECLARE @varname int;   
SET @varname = NULL;  
  
SELECT a  
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO

Теперь установите параметр ANSI_NULLS в значение ON и выполните тестирование.

PRINT 'Testing ANSI_NULLS ON';  
SET ANSI_NULLS ON;  
GO  
DECLARE @varname int;  
SET @varname = NULL  
  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO

Теперь установите параметр ANSI_NULLS в значение OFF и выполните тестирование.

PRINT 'Testing ANSI_NULLS OFF';  
SET ANSI_NULLS OFF;  
GO  
DECLARE @varname int;  
SET @varname = NULL;  
SELECT a   
FROM t1   
WHERE a = @varname;  
  
SELECT a   
FROM t1   
WHERE a <> @varname;  
  
SELECT a   
FROM t1   
WHERE a IS NULL;  
GO  
  
-- Drop table t1.  
DROP TABLE dbo.t1;

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