SET ANSI_NULLS (Transact-SQL)

Область применения:SQL Server (все поддерживаемые версии) База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics Система платформы аналитики (PDW)

Задает совместимое со стандартом ISO поведение операторов сравнения "равно" (=) и "не равно" (<>) при их использовании со значениями NULL в SQL Server.

Синтаксические обозначения в Transact-SQL

Синтаксис

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

SET ANSI_NULLS { ON | OFF }

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

SET ANSI_NULLS ON

Примечание

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Remarks

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

Если параметр ANSI_NULLS имеет значение OFF, операторы "равно" (=) и "не равно" (<>) не следуют стандарту ISO. Инструкция SELECT, использующая предложение WHERE column_name = NULL, вернет строки, имеющие значения 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	UNKNOWN	TRUE
1 = NULL	UNKNOWN	FALSE
NULL <> NULL	UNKNOWN	FALSE
1 <> NULL	UNKNOWN	true
NULL > NULL	UNKNOWN	UNKNOWN
1 > NULL	UNKNOWN	UNKNOWN
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 равно OFF, SQL Server не обрабатывает значения индексов вычисляемых столбцов или представлений и произведет выборку, словно этих индексов не существовало.

Примечание

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

При соединении с драйвером ODBC для Native Client SQL Server или поставщика OLE DB для Native Client 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;  

См. также:

Инструкции SET (Transact-SQL)
SESSIONPROPERTY (Transact-SQL)
= (равно) (Transact-SQL)
IF...ELSE (Transact-SQL)
<> (Не равно) (Transact-SQL)
Инструкции SET (Transact-SQL)
SET ANSI_DEFAULTS (Transact-SQL)
Предложение WHERE (Transact-SQL)
WHILE (Transact-SQL)