SET ANSI_NULLS (Transact-SQL)

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

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

• SET ANSI_NULLS ON— вычисляет и то, {expression} = NULL и другое{expression}, как False если бы значение равноNULL{expression} <> NULL. Это поведение соответствует ANSI.
• SET ANSI_NULLS OFF— оценивается {expression} = NULL как и {expression} <> NULL как FalseTrue если бы значение {expression} равноNULL. Это поведение не рекомендуется, так как NULL значения не должны сравниваться с использованием = и <> операторами.

Примечание.

SET ANSI_NULLS OFF ANSI_NULLS OFF и параметр базы данных не рекомендуется. Начиная с 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, инструкция, использующая WHERE column_name = NULL ноль строк, SELECT даже если в column_name есть значения NULL. Оператор SELECT , использующий WHERE column_name <> NULL ноль строк, даже если в column_name есть значения, отличные от NULL.

Если ANSI_NULLS имеет значение OFF, операторы сравнения equals (=) и Not Equal To (<>) не соответствуют стандарту 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	НЕИЗВЕСТНО	ПРАВДА
1 = NULL	НЕИЗВЕСТНО	ЛОЖЬ
NULL <> NULL	НЕИЗВЕСТНО	ЛОЖЬ
1 <> NULL	НЕИЗВЕСТНО	ПРАВДА
NULL > NULL	НЕИЗВЕСТНО	НЕИЗВЕСТНО
1 > NULL	НЕИЗВЕСТНО	НЕИЗВЕСТНО
NULL IS NULL	ПРАВДА	ПРАВДА
1 IS NULL	ЛОЖЬ	ЛОЖЬ
NULL IS NOT NULL	ЛОЖЬ	ЛОЖЬ
1 IS NOT NULL	ПРАВДА	ПРАВДА

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

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

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

ANSI_NULLS при создании или изменении индексов для вычисляемых столбцов или индексированных представлений также должно быть включено. Если параметр SET ANSI_NULLS имеет значение OFF, любые CREATEUPDATEINSERTинструкции , и 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 собственного клиента 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 или 0null значениями в переменной.

SET ANSI_NULLS OFF
DECLARE @var INT = NULL
SELECT
    IIF(@var = NULL,  'True', 'False') as EqualNull,
    IIF(@var <> NULL, 'True', 'False') as DifferentNull,
    IIF(@var = 0,     'True', 'False') as EqualZero,
    IIF(@var <> 0,    'True', 'False') as DifferentZero

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

EqualNull	DifferentNull	EqualZero	DifferentZero
Верно	Неправда	Неправда	Верно

При SET ANSI_NULLS ON использовании всех выражений будет оцениваться как False, так как NULL их нельзя сравнить с NULL этими операторами или 0 использовать их.

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

-- 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)
• ЕСЛИ... ELSE (Transact-SQL)
• <> (Не равно) (Transact-SQL)
• SET ANSI_DEFAULTS (Transact-SQL)
• ГДЕ (Transact-SQL)
• WHILE (Transact-SQL)