Problem
The definition says:
Does this imply that there will be no nulls in this query?
SELECT Region
FROM employees
WHERE Region = @region
Or do ANSI NULLs just apply to searches like this (where the WHERE clause includes the term NULL)?
SELECT Region
FROM employees
WHERE Region = NULL
Asked by Rodniko
Solution #1
When used in your first example, it means that no rows will be returned if @region is NULL, even if there are entries in the table where Region is NULL.
When ANSI NULLS is enabled (which you should always enable because the option to disable it in the future will be deleted), every comparison operation with (at least) one NULL operand outputs the third logic value – UNKNOWN (as opposed to TRUE and FALSE).
If they aren’t previously decided (e.g. AND with a FALSE operand or OR with a TRUE operand), UNKNOWN values propagate through any combining boolean operators or negations (NOT).
The WHERE clause is used to filter the FROM clause’s result set, with the overall value of the WHERE clause having to be TRUE in order for the row to not be filtered out. As a result, every comparison that returns a UNKNOWN will result in the row being filtered out.
The following is a quote from @user1227804’s response:
from SET ANSI_NULLS*
However, I’m not sure what point it’s attempting to make, because comparing two NULL columns (for example, in a JOIN) still fails:
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
The query above produces 0 results, whereas:
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 )
One row is returned. NULL does not equal NULL, even when both operands are columns. Furthermore, the documentation for = says nothing about the operands:
Both 1 and 2 are, however, erroneous; the outcome of both comparisons is UNKNOWN.
*It took years for the obscure meaning of this text to be found. What this means is that the setting has no effect on those comparisons, and it always acts as if the setting is ON. It would have been clearer if it had specified that the setting SET ANSI NULLS OFF had no effect.
Answered by Damien_The_Unbeliever
Solution #2
If ANSI NULLS is set to “ON” and we use =,> on the NULL column value in a select statement, no results will be returned.
Example
create table #tempTable (sn int, ename varchar(50))
insert into #tempTable
values (1, 'Manoj'), (2, 'Pankaj'), (3, NULL), (4, 'Lokesh'), (5, 'Gopal')
SET ANSI_NULLS ON
select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (0 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (0 row(s) affected)
SET ANSI_NULLS OFF
select * from #tempTable where ename is NULL -- (1 row(s) affected)
select * from #tempTable where ename = NULL -- (1 row(s) affected)
select * from #tempTable where ename is not NULL -- (4 row(s) affected)
select * from #tempTable where ename <> NULL -- (4 row(s) affected)
Answered by Pravat Behuria
Solution #3
If @Region is not null (for example, @Region = ‘South’), no rows with a null Region field will be returned, regardless of the value of ANSI NULLS.
Only when the value of @Region is null, i.e. when your first query effectively becomes the second, will ANSI NULLS make a difference.
Because null = null yields an unknown boolean value (a.k.a. null), ANSI NULLS ON will not return any rows in such scenario. ANSI NULLS OFF returns any rows with a null Region field (since null = null returns true).
Answered by SWeko
Solution #4
SET ANSI_NULLS ON
IT returns all values in the table, including null values.
SET ANSI_NULLS off
It comes to an end when one or more columns contain null values.
Answered by Joseph Stalin
Solution #5
The essential point here, I believe, is:
Never user:
Always use:
Answered by user369142
Post is based on https://stackoverflow.com/questions/9766717/in-sql-server-what-does-set-ansi-nulls-on-mean