4

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Royi Namir
  • 144,742
  • 138
  • 468
  • 792

4 Answers4

7
  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.
dpw
  • 1,576
  • 9
  • 14
  • 1
    your query doesnt work , as opposed to Hitesh'es http://i.stack.imgur.com/HufaY.jpg – Royi Namir Jun 30 '12 at 09:07
  • @RoyiNamir: How about specifying *your* database's name? :) – Andriy M Jun 30 '12 at 09:09
  • 1
    (Just for completeness) The documentation also says: `In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error`. – Andriy M Jun 30 '12 at 09:10
  • @RoyiNamir: `0` meaning `OFF`, I presume? – Andriy M Jun 30 '12 at 09:12
  • @RoyiNamir: yes, you have to change 'MyDatabaseName' to the name of the database you're checking the value for. – dpw Jun 30 '12 at 09:12
  • @AndriyM yes , and the former solution says its on. ( which is true) – Royi Namir Jun 30 '12 at 09:12
  • @RoyiNamir: It's off for the DB and on for the session, probably. – Andriy M Jun 30 '12 at 09:13
  • @RoyiNamir: `SET ANSI_NULLS` changes the setting for the *session* and doesn't affect it for the *database* (the session's setting takes precedence over the database's, that's what Dan said in #3). To change it for the database, you use `ALTER DATABASE` with a corresponding clause. – Andriy M Jun 30 '12 at 09:19
  • 1
    @AndriyM yeah i was just gonna tell you guys - the final solution. Dan's solution is working ONLY regards setting the DB option while Hitesh solution is for the SESSION ONLY - ignoring the DB option value . ( 100% after testing). – Royi Namir Jun 30 '12 at 09:21
  • now im having trouble selecting the selected answer but i guess my question hasnt been asked right ( due to - unknowing it can be set on session && db).....so i will select the one who answered ALL the 3 question. – Royi Namir Jun 30 '12 at 09:25
  • Why is the default in SQL Server 2012 ANSI_NULLS OFF (false in database options) if they're going to force it ON in future versions? – Triynko Oct 16 '13 at 17:45
5

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/

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Hitesh Patel
  • 439
  • 2
  • 10
2

you can use this query to get the property of a given database:

SELECT is_ansi_nulls_on FROM sys.databases WHERE name = 'yourDatabase'
Sebastian
  • 41
  • 4
0

You can check all the settings by below metioned

dbcc useroptions

Baskaran
  • 1