В SQL Server, что означает" установить ANSI NULLS ON"?

определение говорит:

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

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

SELECT Region
FROM employees
WHERE Region = @region

или ANSI_NULLs касается только таких запросов, как этот (где же WHERE включает в себя специфические слова NULL)?

SELECT Region
FROM employees
WHERE Region = NULL
7 60
sql tsql stored-procedures

7 ответов:

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

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

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

The 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, результат NULL1, следуя конвенции ANSI, что a NULL (или неизвестно) значение не равно другому NULL или неизвестное значение.

если ANSI_NULLS установлено значение OFF, результат NULL по сравнению с NULL - это TRUE.

сравнение NULL доNULL значение всегда приводит к FALSE2.

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

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

46

УСТАНОВИТЬ QUOTED_IDENTIFIER ВКЛ / ВЫКЛ

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

когда он установлен в ON любой набор символов, определенный в двойные кавычки ""составляет обрабатывается как идентификатор T-SQL (Имя Таблицы, Имя Процесса, Имя Столбца....и т. д.)

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

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

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

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.

УСТАНОВИТЬ ANSI_NULLS ВКЛ / ВЫКЛ:

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

когда он установлен в ON С NULL с помощью = и приведет к ложному значению. И это стандартное поведение, определенное ISO. Поэтому для сравнения с нулевыми значениями нам нужно использовать значение NULL и не NULL.

когда он установлен в выкл С NULL using = и будет работать как обычно, т. е. NULL = NULL возвращает true и 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.
28

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

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

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

4

УСТАНОВИТЕ ANSI_NULLS НА

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

SET ANSI_NULLS off

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

1

установить заполнение ANSI будет сделать нуль = нуль сравнения возвращают true. Например:

        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
0

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

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

для электронной.г

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 !
0

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

пример

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

вставить в #tempTable

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

ОБЪЕДИНЕНИЕ ВСЕХ

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

ОБЪЕДИНЕНИЕ ВСЕХ

выберите 3, NULL

Союз Все

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

ОБЪЕДИНЕНИЕ ВСЕХ

выберите 5, 'Gopal'

УСТАНОВИТЬ ANSI_NULLS НА

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

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

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

SET ANSI_NULLS OFF

выбрать * из #поддающийся соблазну, где эмаль кулон имеет значение null -- (1 ряд(ов), пострадавших)

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

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

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

-2