CODEWITHSUNDEEP
sql-servert-sqlsql-null
George Menoutis
George Menoutis

Reputation: 7240

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

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)

Upvotes: 2

Views: 703

Answers (2)

lptr
lptr

Reputation: 6788

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;

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453028

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.

Upvotes: 2

Related Questions