ANSI_NULLS flag in SQL Server?
sql-server, sql-server-2005, t-sql
Solution
- `select databasepropertyex('MyDatabaseName', 'IsAnsiNullsEnabled')` will tell you the database default. Hitesh's answer will tell you the value for the current session.
- The database has a default settting, and each session can override the database default.
- 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.
Problem
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.