ANSI_NULLS flag in SQL Server?

Question

I can change the value of SET ANSI_NULLS by using SET ANSI_NULLS OFF or SET ANSI_NULLS ON

Question #1 how can I get the current value ?

Question #2 does setting a value is applied to query? Table? Instance? Whole db?

Question #3 If I'm entering a company which does if myNullParam <>null, what is the place which I should check (db ? schema? query ?) to tell them - this gonna work , or Not ?

thanks.

Answer 1

1. select databasepropertyex('MyDatabaseName', 'IsAnsiNullsEnabled') will tell you the database default. Hitesh's answer will tell you the value for the current session.
2. The database has a default settting, and each session can override the database default.
3. The session value. However, the MSDN documentation says For a script to work as intended, regardless of the ANSI_NULLS database option or the setting of SET ANSI_NULLS, use IS NULL and IS NOT NULL in comparisons that might contain null values. So while it may work, it's certainly against best practices.

Answer 2

Answer 1

You can run below query to get if ANSI_NULLS is set or not.

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

if prints ANSI_NULLS then ANSI_NULLS is set or it is not set.

you can find more help here http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/