What does “SET ANSI_NULLS ON” mean in SQL Server?

Question

The definition says:

When SET ANSI_NULLS is ON, the SELECT statement, which uses WHERE column_name = NULL, returns null rows, even if the column has null values. The SELECT statement, which uses WHERE column_name <> NULL, returns zero rows, even if the column has non-zero values.

Does this mean that no zeros will be included in this query?

SELECT EmployeeID, LastName, FirstName, Region FROM employees WHERE Region=@region 

Or do ANSI_NULL apply only to such queries (where WHERE contains the specific word null )?

 SELECT EmployeeID, LastName, FirstName, Region FROM employees WHERE Region=null 

Answer 1

This means that no rows will be returned if @region is NULL , if they are used in your first example, even if the table has rows in which Region is NULL .

When ANSI_NULLS enabled (which you should always set in any case, since the option not to enable it in the future will be removed), any comparison operation, where (at least) one of the NULL operands yields a third logical value - UNKNOWN (unlike TRUE and FALSE ).

UNKNOWN values ​​are propagated through any union of Boolean operators if they are not already defined (for example, AND with the FALSE operand or OR with the TRUE operand) or negatives ( NOT ).

The WHERE used to filter the result set created by the FROM , so the total value of the WHERE must be TRUE so that the row is not filtered. Thus, if UNKNOWN is created by any comparison, this will filter the string.

---

@ user1227804 answer includes this quote:

If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

from SET ANSI_NULLS ^*

However, I'm not sure at what point it is trying to make, because if you compare two NULL columns (for example, in JOIN ), the comparison 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 above query returns 0 rows, 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) 

Returns a single row. Therefore, even if both operands are columns, NULL not equal to NULL . And the documentation for = not allowed to talk about operands:

When comparing two NULL expressions, the result depends on the ANSI_NULLS parameter:

If ANSI_NULLS set to ON , the result is NULL ¹ following the ANSI convention that NULL (or unknown) is not equal to another NULL or unknown value.

If ANSI_NULLS set to OFF , the result is NULL compared to NULL equal to TRUE .

Comparing NULL with a non- NULL always results in FALSE ² .

However, both ¹ and ² are incorrect - the result of both UNKNOWN comparisons.

---

^{* The} mysterious meaning of this text was finally discovered years later. In fact, this means that the setting does not work for these comparisons, and it always acts as if the setting were turned on. It would be clearer if he stated that SET ANSI_NULLS OFF is a parameter that did not affect.

Answer 2

SET QUOTED_IDENTIFIER ON / OFF

Specifies how SQL Server processes data specified in single quotes and double quotes.

If it is set to ON , any character set specified in double quotation marks "is treated as a T-SQL identifier (Table Name, Proc Name, Column Name .... cc)

If any character set defined in single quotes is treated as a literal.

 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 

If it is set to OFF , any character set defined either in single quotes or in double quotes is treated as 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:

The ANSI_NULLS parameter specifies how SQL Server handles comparison operations with NULL values.

If set to ON , any comparison with NULL using = and <> will result in a false value . And this is the standard behavior defined by ISO. Therefore, to compare with NULL values, we need to use IS NULL and IS NOT NULL.

If it is set to OFF , any comparison with NULL using = and <> will work as usual, that is, NULL = NULL returns true and 1 = NULL returns 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. 

Answer 3

If @Region not null (say @Region = 'South' ), it will not return rows where the Region field is null, regardless of ANSI_NULLS.

ANSI_NULLS will only have value when @Region is null , i.e. when your first request essentially becomes second.

In this case, ANSI_NULLS ON will not return rows (because null = null will result in an unknown boolean value (aka null )), and ANSI_NULLS OFF will return any rows where Region is null (because null = null will give true )

Answer 4

Set ANSI NULLS OFF to make NULL = NULL compareide true. EG:

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

will return some result as follows: zcwInvoiceDeliveryType 744547 NULL zcExpenseRptStatusTrack 2099048 NULL ZCVendorPermissions 2840564 NULL ZCWOrgLevelClientFee 4322525 NULL

So far, this query will not return any results:

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

Answer 5

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

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns null rows, even if the column has null values. The SELECT statement, which uses WHERE column_name <> NULL, returns zero rows, even if the column has non-zero values.

For example,

 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 ! 

Answer 6

SET ANSI_NULLS ON

IT Returns all values, including null values ​​in the table.

SET ANSI_NULLS off

it Ends when columns contain null values

Answer 7

If ANSI_NULLS is set to "ON", and if we use value =, <> in the NULL value of the column when writing the select statement, then it will not return any result.

Example

create table #tempTable (sn int, ename varchar (50))

paste in #tempTable

select 1, 'Manoj'

UNION ALL

select 2, 'Pankaj'

UNION ALL

select 3, NULL

UNION ALL

select 4, 'Lokesh'

UNION ALL

select 5, 'Gopal'

SET ANSI_NULLS ON

select * from #tempTable, where ename is NULL - (1 row affected)

select * from #tempTable, where ename = NULL - (0 rows affected)

select * from #tempTable, where ename <> NULL - (0 rows affected)

SET ANSI_NULLS OFF

select * from #tempTable, where ename is NULL - (1 row affected)

select * from #tempTable where ename = NULL - (1 row (s) affected)

select * from #tempTable where ename is not NULL - (4 lines affected)

select * from #tempTable, where ename <> NULL - (4 lines affected)