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

Ответ 1

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

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

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

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

@user1227804 answer включает эту цитату:

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

из 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 ¹ следуя соглашению ANSI о том, что значение NULL (или неизвестное) не равно другому NULL или неизвестное значение.

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

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

Однако как ¹ так и ² неверны - результат обоих сравнений UNKNOWN.

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

Ответ 2

SET QUOTED_IDENTIFIER ВКЛ/ВЫКЛ

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

Если для него установлено значение ВКЛ, любой набор символов, указанный в двойных кавычках ", рассматривается как идентификатор T-SQL (Имя таблицы, Имя Proc, Имя столбца....cc)

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

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

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

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.

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

Если для него установлено значение ВЫКЛ, любое сравнение с NULL using = и < > будет работать как обычно, то есть NULL = NULL возвращает true и 1 = NULL возвращает false.

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.

Ответ 3

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

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

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

Ответ 4

Установите ANSI NULLS OFF, чтобы сделать NULL = NULL compareide 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

Ответ 5

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

Когда SET ANSI_NULLS ВКЛЮЧЕН, оператор SELECT, который использует WHERE column_name = NULL, возвращает нулевые строки, даже если в столбце есть нулевые значения. Оператор SELECT, который использует WHERE column_name < > NULL, возвращает нулевые строки, даже если в столбце есть ненулевые значения.

Например,

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 !

Ответ 6

SET ANSI_NULLS ON

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

SET ANSI_NULLS off

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

Ответ 7

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

Пример

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

вставить в #tempTable

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

UNION ALL

выберите 2, 'Pankaj'

UNION ALL

выберите 3, NULL

UNION ALL

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

UNION ALL

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

SET ANSI_NULLS ON

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

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

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

SET ANSI_NULLS OFF

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

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

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

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