Использование SET ANSI_NULLS в SQL

Время прочтения: 5 мин.

При обработке массивов данных, в процессе создания хранимых процедур, возникает необходимость сравнения нулевых значений. Многие разработчики путаются в обработке нулевых значений. Когда говорится о значении, равном NULL, то на самом деле имеется в виду, что оно не имеет никакого значения. Это сильно отличается от числа равного нулю, или строки, являющейся пустой (или нулевой длинны). Именно поэтому нет возможности сравнить значение с NULL используя базовые операторы сравнения IS NULL или IS NOT NULL. Значение NULL – это состояние, в котором находится переменная или столбец, а не значение, которое они содержат.

Решение данной задачи возможно осуществить с помощью встроенного функционала настройки драйвера ODBC и поставщика OLE DB клиента SQL Server, для этого воспользуюсь инструкцией SET ANSI_NULLS, которая позволяет настроить SQL Server для использования операторов сравнения со значениями NULL, установив параметры SET ANSI_NULLS ON или SET ANSI_NULLS OFF.

Рассмотрю на примерах функциональные возможности использования данных настроек.

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

Например, при значении параметра инструкции ANSI_NULLS равном ON в инструкции SELECT с условием WHERE столбец = NULL не вернет ни одной строки, если в столбце имеются значения NULL. Инструкция SELECT с условием WHERE столбец <> NULL не вернет ни одной строки, даже если в столбце есть значения, отличные от NULL. При значении параметра ANSI_NULLS равном OFF операторы равенства (=) и неравенства (<>) не следуют стандарту ISO. SELECT с условием WHERE столбец = NULL вернет строки со значениями NULL в столбце. Инструкция SELECT с условием WHERE столбец <> NULL возвращает строки содержащие значения, отличные от NULL, в столбце. Также любая инструкция SELECT с условием WHERE столбец <>ABC_value, вернет все строки содержащими значения, не равными ABC_value и не равными NULL. Если параметр ANSI_NULLS имеет значение ON, все сравнения со значением NULL вернут значение UNKNOWN, а когда ANSI_NULLS имеет значение OFF, сравнение любых значений с NULL вернет TRUE только в том случае, если сравниваемое значение тоже NULL. Если параметр ANSI_NULLS не указан, применяется значение ANSI_NULLS текущей базы данных.

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

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

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

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

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

SQL Server не обрабатывает значения индексов вычисляемых столбцов или представлений и производит выборку, словно этих индексов не существовало.

При работе с вычисляемыми столбцами или индексированными представлениями значение ANSI_NULLS является одним из обязательных параметров директивы SET, которые должны быть настроены следующим образом, параметры ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL должны иметь значение ON, а параметр NUMERICROUNDABORT значение OFF. Данные настройки обусловлены тем, что SQL Server возвратит сообщение об ошибке при обнаружении ошибок деления на ноль и переполнения. При отсутствии вышеупомянутых настроек данные ошибки будут игнорироваться.

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

SELECT SESSIONPROPERTY ('ANSI_NULLS') AS 'CURRENT VALUE OF SET ANSI_NULLS'

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

PRINT ’Создаю тестовую таблицу’
CREATE TABLE DB_TEST_ANSI_NULLS ([Value_field] int NULL)
INSERT INTO DB_TEST_ANSI_NULLS values (NULL),(0),(1)
GO

Следующий этап

Использую стандартный запрос на выборку с параметром ANSI_NULLS по умолчанию (значение ON) и при значении ANSI_NULLS OFF (SET ANSI_NULLS OFF)

DECLARE @varname int;
  SET @varname = NULL;

SELECT [Value_field]
  FROM [DB_TEST_ANSI_NULLS]
  WHERE [Value_field]  =  @varname;

SELECT [Value_field]
  FROM [DB_TEST_ANSI_NULLS]
  WHERE [Value_field] <>  @varname;

SELECT [Value_field]
  FROM [DB_TEST_ANSI_NULLS]
  WHERE [Value_field] IS NULL;

  SELECT [Value_field]
  FROM [DB_TEST_ANSI_NULLS]
  WHERE [Value_field] IS NOT NULL;
GO

Получается следующий результат:

       ANSI_NULLS ON              ANSI_NULLS OFF

Большая эффективность использования данной процедуры возникает при создании сложных конструкций, при JOINах т.д.

Но, резонно можно заявить, что в SQL есть процедура IS NULL(). Да, есть, но, к сожалению, данная процедура обрабатывает значения равные NULL и не воспринимает пустые ячейки, что приводит к потерям при обработке данных. Так что, во избежание потери данных при их фильтрации, рекомендую использовать ANSI_NULLS.

Спасибо за уделенное время, надеюсь, что мой опыт позволит исключить потери, повысить качество и полноту обработки данных!