SET ANSI_NULLS (Transact-SQL)

Статья
03/07/2014

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

Важно!
В будущей версии параметр SQL Server ANSI_NULLS всегда будет иметь значение ON, а приложения, явно присваивающие ему значение OFF, будут вызывать ошибку.Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется.

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

Применимо для следующих объектов: SQL Server (начиная с SQL Server 2008 до текущей версии).

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

SET ANSI_NULLS { ON | OFF }

Замечания

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

Если SET 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.

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

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

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

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

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

Когда параметр SET ANSI_DEFAULTS имеет значение ON, режим SET ANSI_NULLS включен.

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

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

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

Разрешения

Необходимо членство в роли public.

Примеры

Следующий пример иллюстрирует использование операторов «равно» (=) и «не равно» (<>) для сравнения со значениями NULL и не 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

-- SET ANSI_NULLS to ON and test.
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

-- SET ANSI_NULLS to OFF and test.
PRINT 'Testing SET 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;