Время прочтения: 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_NULS ON | Параметр SET ANSI_NULS 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. Значение 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.
Спасибо за уделенное время, надеюсь, что мой опыт позволит исключить потери, повысить качество и полноту обработки данных!