ansi_nulls: some ways to check it don't seem to work

Question

The documentation states that the database ANSI_NULLS flag controls the behavior of comparisons with null on some side.

I was checking this stack overflow post to check how to determine (not set) the value of this flag. Interestingly, not all of the answers seemed to work for me.

My test query:

create table x(id int,txt nvarchar(max))
insert x(id,txt) values (1,'not null here'),(2,null)
select * from x

--query 1
select * from x where txt=null

--query 2
select * from x where txt<>null

The output returned is two empty result sets. From this, I can logically deduce ANSI_NULLS is ON in my database.

Now, to the checking options:

1

select databasepropertyex('MyDatabaseName', 'IsAnsiNullsEnabled')

Returns 0. I did not expect this.

2

DECLARE @options INT
SELECT @options = @@OPTIONS
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'

Prints "ANSI_NULLS". Expected.

3

SELECT is_ansi_nulls_on FROM sys.databases WHERE name = 'MyDatabaseName'

Returns 0. I did not expect this.

4

dbcc useroptions

The results set includes a row with [Set Option]='ansi_nulls' and [Value]='Set'. Expected.

Why do do options 1 and 3 give me this result?

My @@version is:

Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) 
    Jun 15 2019 00:26:19 
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Because the connection setting overrides the database default — Martin Smith, May 08 '20 at 14:22
@Martin Smith Can you post an answer with more details, please? How can I check the connection setting? I guess this question is dependent on the application used, so let me clarify that I am asking about SSMS. — George Menoutis, May 08 '20 at 14:25
I strongly, however, recommend never turning ANSI NULLs off. The ability to do so is to be removed from SQL Server anyway, and `NULL = NULL` is *meant* to **not** be true. — Thom A, May 08 '20 at 14:26
If you want to compare with NULL, let's write this: ** select * from x where EXISTS(SELECT txt INERSECT SELECT null) ** — Scrappy Coco, May 08 '20 at 14:27
@Larnu I am aligned with your opinion. Actually, this whole research is just to make me feel "secure" that I can write code which can safely assume this flag being always on (at least when nooone changed it on purpose) — George Menoutis, May 08 '20 at 14:28
You will get a result set when you change "=null" and "<>null" to "is null" and "is not null" — Larry Beasley, May 08 '20 at 14:58

Martin Smith · Accepted Answer · 2020-05-08T15:45:34.930

Your queries are looking at two different things.

One is the database default (can be changed with ALTER DATABASE SET) - the other is what it is set to in the current session.

The database default is practically useless as all common ways of connecting to SQL Server set ANSI_NULLS on as described below

Connection-level settings that are set by using the SET statement override the default database setting for ANSI_NULLS. ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULLS to ON for the session, by default. The clients run the statement when you connect to an instance of SQL Server. For more information, see SET ANSI_NULLS.

score 0 · Answer 2 · answered May 08 '20 at 17:25

for the session:

set ansi_nulls on;

select sessionproperty('ANSI_NULLS');

select ansi_nulls
from sys.dm_exec_sessions
where session_id = @@spid;

select case when null=null then 0 else 1 end;
-------------
set ansi_nulls off;

select sessionproperty('ANSI_NULLS');

select ansi_nulls
from sys.dm_exec_sessions
where session_id = @@spid;

select case when null=null then 0 else 1 end;

ansi_nulls: some ways to check it don't seem to work

1

2

3

4

2 Answers2