Что означает "SET ANSI_NULLS ON" в SQL Server?

Определение гласит:

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

Значит ли это, что в этот запрос не будут включены нули?

SELECT Region
FROM employees
WHERE Region = @region

Или сделать ANSI_NULLкасается только таких запросов, как этот (где WHERE включает в себя конкретное слово NULL)?

SELECT Region
FROM employees
WHERE Region = NULL
Источник

9 ответов

Решение

Это означает, что строки не будут возвращены, если @region является NULL при использовании в первом примере, даже если в таблице есть строки, где Region является NULL,

когда ANSI_NULLS включен (который вы должны всегда устанавливать в любом случае, так как опция не включать его будет удалена в будущем), любая операция сравнения, в которой (по крайней мере) один из операндов NULL производит третье логическое значение - UNKNOWN (в отличие от TRUE а также FALSE).

UNKNOWN значения распространяются через любые объединяющие логические операторы, если они еще не определены (например, AND с FALSE операнд или OR с TRUE операнд) или отрицания (NOT).

WHERE Предложение используется для фильтрации результирующего набора, созданного FROM пункт, так что общая стоимость WHERE пункт должен быть TRUE чтобы строка не была отфильтрована. Итак, если UNKNOWN производится любым сравнением, это приведет к тому, что строка будет отфильтрована.

@ user1227804 ответ включает в себя эту цитату:

Если обе стороны сравнения являются столбцами или составными выражениями, настройка не влияет на сравнение.

от SET ANSI_NULLS *

Тем не менее, я не уверен, что он пытается сделать, так как если два NULL столбцы сравниваются (например, в JOIN), сравнение по-прежнему не удается:

create table #T1 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T1(ID,Val1) select 1,null

create table #T2 (
    ID int not null,
    Val1 varchar(10) null
)
insert into #T2(ID,Val1) select 1,null

select * from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID and t1.Val1 = t2.Val1

Приведенный выше запрос возвращает 0 строк, тогда как:

select * from #T1 t1 inner join #T2 t2 on t1.ID = t2.ID and (t1.Val1 = t2.Val1 or t1.Val1 is null and t2.Val1 is null)

Возвращает одну строку. Так что даже когда оба операнда являются столбцами, NULL не равно NULL, И документация для = нечего сказать об операндах:

Когда вы сравниваете два NULL выражений, результат зависит от ANSI_NULLS установка:

Если ANSI_NULLS установлен в ON результат NULL 1, в соответствии с соглашением ANSI, что NULL (или неизвестно) значение не равно другому NULL или неизвестное значение.

Если ANSI_NULLS установлен в OFF, результат NULL по сравнению с NULL является TRUE,

Сравнение NULL не NULL значение всегда приводит к FALSE 2

Тем не менее, оба 1 и 2 неверны - результат обоих сравнений UNKNOWN,

* Загадочный смысл этого текста был наконец обнаружен спустя годы. На самом деле это означает, что для этих сравнений настройка не действует и всегда действует так, как если бы настройка была включена. Было бы яснее, если бы он заявил, что SET ANSI_NULLS OFF была настройка, которая не имела никакого влияния.

Источник

Если для ANSI_NULLS установлено значение "ON" и если мы применяем =, <> к значению столбца NULL во время записи оператора select, то он не вернет никакого результата.

пример

создать таблицу #tempTable (sn int, ename varchar(50))

вставить в #tempTable

выберите 1, "Manoj"

СОЮЗ ВСЕХ

выберите 2, "Панкадж"

СОЮЗ ВСЕХ

выберите 3, NULL

СОЮЗ ВСЕХ

выберите 4, "Lokesh"

СОЮЗ ВСЕХ

выберите 5, "Гопал"

SET ANSI_NULLS ON

выберите * из #tempTable, где значение ename равно NULL - (затронуто 1 строк)

выберите * из #tempTable, где ename = NULL - (затронуто 0 строк)

select * from #tempTable, где ename <> NULL - (затронуто 0 строк)

SET ANSI_NULLS OFF

выберите * из #tempTable, где значение ename равно NULL - (затронуто 1 строк)

выберите * из #tempTable, где ename = NULL - (затронут 1 строка)

select * from #tempTable, где ename не NULL - (затронуто 4 строк)

выберите * из #tempTable, где ename <> NULL - (затронуто 4 строк)

Источник

SET QUOTED_IDENTIFIER ON/OFF

Он определяет, как SQL Server обрабатывает данные, определенные в одинарных и двойных кавычках.

Когда он установлен в положение ON, любой набор символов, определенный в двойных кавычках "", обрабатывается как идентификатор T-SQL (имя таблицы, имя процесса, имя столбца… и т. Д.)

Когда любой набор символов, определенный в одинарных кавычках, '' рассматривается как литерал.

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" int)  -- SUCCESS
GO

SET QUOTED_IDENTIFIER ON
SELECT "sometext" AS Value   -- FAIL because “sometext” is not a literal

Если для него установлено значение OFF, любой набор символов, определенный в одинарных или двойных кавычках, рассматривается как литерал.

SET QUOTED_IDENTIFIER OFF
CREATE TABLE "SELECT"(“TABLE” int) -- FAIL
GO

SET QUOTED_IDENTIFIER OFF
SELECT "sometext" AS Value    -- SUCCESS as “sometext” is treated litral

--The default behavior is ON in any database.

SET ANSI_NULLS ON / OFF:

Параметр ANSI_NULLS указывает, как SQL Server обрабатывает операции сравнения со значениями NULL.

Когда установлено значение ON, любое сравнение с NULL с использованием = и <> приведет к ложному значению. И это стандартное поведение ISO. Поэтому для сравнения значений NULL нам нужно использовать IS NULL и IS NOT NULL.

Когда он установлен на OFF, любое сравнение с NULL с использованием = и <> будет работать как обычно, т.е. NULL = NULL возвращает истину, а 1= NULL возвращает ложь.

SET ANSI_NULLS ON
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  different

SET ANSI_NULLS ON
IF NULL IS NULL
 PRINT 'same'
ELSE
 PRINT 'different'
-- result: same
--==============================
SET ANSI_NULLS OFF
IF NULL = NULL
 PRINT 'same'
ELSE
 PRINT 'different'
--result:  same (now NULL = NULL works as 1=1)

--The default behavior is ON in any database.
Источник

Думаю, здесь главное:

Никогда не пользователь:

  • @anything = NULL
  • @anything <> NULL
  • @anything != null

Всегда используйте:

  • @anything IS NULL
  • @anything IS NOT NULL
Источник

Если @Region это не null значение (скажем, @Region = 'South') он не будет возвращать строки, в которых поле Region имеет значение null, независимо от значения ANSI_NULLS.

ANSI_NULLS будет иметь значение только тогда, когда значение @Region является nullт.е. когда ваш первый запрос по сути становится вторым.

В этом случае ANSI_NULLS ON не будет возвращать никаких строк (потому что null = null даст неизвестное логическое значение (иначе null)) и ANSI_NULLS OFF вернет все строки, где поле Region имеет значение null (потому что null = null даст true)

Источник

SET ANSI_NULLS ON

IT возвращает все значения, включая нулевые значения в таблице

ВЫКЛЮЧИТЬ ANSI_NULLS

Заканчивается, когда столбцы содержат нулевые значения

Источник

Если для параметра ANSI NULLS установлено значение OFF, значение сравнения NULL = NULL будет равно true. EG:

        SET ANSI_NULLS OFF
        select * from sys.tables
        where principal_id = Null

вернет некоторый результат, как показано ниже: zcwInvoiceDeliveryType 744547 NULL zcExpenseRptStatusTrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525 NULL

Пока этот запрос не вернет никаких результатов:

        SET ANSI_NULLS ON 
        select * from sys.tables
        where principal_id = Null
Источник

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql

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

Например,

DECLARE @TempVariable VARCHAR(10)
SET @TempVariable = NULL

SET ANSI_NULLS ON
SELECT 'NO ROWS IF SET ANSI_NULLS ON' where    @TempVariable = NULL
-- IF ANSI_NULLS ON , RETURNS ZERO ROWS


SET ANSI_NULLS OFF
SELECT 'THERE WILL BE A ROW IF ANSI_NULLS OFF' where    @TempVariable =NULL
-- IF ANSI_NULLS OFF , THERE WILL BE ROW !
Источник

SET ANSI_NULLS ON не вернет ни одной строки при применении = , <> , !=в каком состоянии. Вам нужно использовать IS NULL , IS NOT NULL.

SET ANSI_NULLS OFF всегда будет возвращать результат при использовании = , <> , != , IS NULL , IS NOT NULL.

Источник
Другие вопросы по тегам