How do I check if ANSI_NULLS is turned on or not?

2013-03-12

Question

_{Tuesday, March 12, 2013 7:41 PM}

Is there a query I can run that will tell me if this setting is on?

All replies (7)

_{Tuesday, March 12, 2013 11:10 PM ✅Answered}

You can use the function sessionproperty():

IF sessionproperty('ANSI_NULLS') = 1
PRINT 'Everything is fine'
ELSE
PRINT 'Cough. It''s dusty in here'

Probably true in most cases, but in one particular case of a procedure that had a where clause comparing columns to parameter values, it was helpful to turn ansi_nulls off inside the procedure at the beginning, then turn it back on at the end.

It would be a lot better idea to fix the procedure.

Be aware of there are several features in SQL Server that requires ANSI_NULLS to be ON.

Erland Sommarskog, SQL Server MVP, [email protected]

_{Tuesday, March 12, 2013 7:44 PM}

Take a look at this

http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Tuesday, March 12, 2013 9:41 PM}

You can use the basic test:

if null=null
  print 'ANSI NULLs are off'
Else
  print 'ANSI NULLs are on'

I would recommend you always turn ANSI NULLs on. You shouldn't want NULL equality comparisons, but instead stick to standard SQL.

Gert-Jan

_{Tuesday, March 12, 2013 9:51 PM}

You can use the basic test:
if null=null
  print 'ANSI NULLs are off'
Else
  print 'ANSI NULLs are on'
I would recommend you always turn ANSI NULLs on. You shouldn't want NULL equality comparisons, but instead stick to standard SQL.

Gert-Jan

Probably true in most cases, but in one particular case of a procedure that had a where clause comparing columns to parameter values, it was helpful to turn ansi_nulls off inside the procedure at the beginning, then turn it back on at the end.

_{Tuesday, March 12, 2013 10:24 PM}

You may handle that without setting that property OFF and ON.

SET ANSI_NULLS OFF DECLARE @col1 VARCHAR(15) =NULL -- 'Black'SELECT *FROM AdventureWorks.Production.ProductWHERE Color=@col1

--OR

SET ANSI_NULLS ON DECLARE @col VARCHAR(15) =NULL -- 'Black'SELECT *FROM AdventureWorks.Production.ProductWHERE (CASE WHEN (Color IS NULL AND @col IS NULL         OR Color=@col)THEN 1 ELSE 0 END)=1

Narsimha

_{Tuesday, March 12, 2013 10:44 PM}

>> Probably true in most cases, but in one particular case of a procedure that had a where clause comparing columns to parameter values, it was helpful to turn ansi_nulls off inside the procedure at the beginning, then turn it back on at the end <<

Never do this; learn to write good SQL instead. Ask Microsoft for the "IS [NOT] DISTINCT FROM" comparison. For now, use COALESCE and default parameter values.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

_{Tuesday, March 19, 2013 9:34 PM | 1 vote}

Here are the ways to check for ANSI NULLS:

PRINT convert(nvarchar,SESSIONPROPERTY('ANSI_NULLS'))
-- 1

DBCC USEROPTIONS;
/*
Set Option  Value
textsize    2147483647
language    us_english
dateformat  mdy
datefirst   7
lock_timeout    -1
quoted_identifier   SET
arithabort  SET
ansi_null_dflt_on   SET
ansi_warnings   SET
ansi_padding    SET
ansi_nulls  SET
concat_null_yields_null SET
isolation level read committed
*/

IF ( (32 & @@options) = 32 ) PRINT 'ANSI_NULLS' 
-- ANSI_NULLS

Kalman Toth Database & OLAP Architect sqlusa.com
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Share via

How do I check if ANSI_NULLS is turned on or not?

Question

All replies (7)

Additional resources