SET ANSI_NULLS (Transact-SQL)
Применимо: 
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform 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
Замечания
Если 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;