Controlling SQL Server 2005 behavior by setting database options regarding NULL values

To access SQL Server there are many Tools and interfaces available, these interfaces can have some default settings to control the database behavior from front end. Right now I am going to talk about most familiar database front end interface SQL Server Management Studio.

To open database Options,

Right Click your database >> Properties >> Options, you will get below screen shot.

ANSI NULL Default:

The default option ANSI NULL DEFAULT corresponds to two session settings ANSI_NULL_DFLT_ON and ANSI_NULL_DFLT_OFF. When ANSI null default database option is false, then the new columns created with the ALTER TABLE and CREATE TABLE statements are by default NOT NULL if Nullability for the column is not explicitly defined.

When this option is set to ON, columns comply with the ANSI SQL-92 rules for column nullability. That is if you don't specifically indicate whether a column in a table allows NULL values, NULLs are allowed. When this option is set to OFF, newly created columns do not allow NULLs if no nullability constrains is specified.

ANSI_NULLS:

Database option ANSI Nulls corresponds to the session settings SET ANSI_NULLS. When this option is set to true, all comparison to a null value evaluate to false. When it is set to false, comparison of non-Unicode values to a null evaluate to true if both values are NULL.

In addition if this option is set to true then your code must use the function IS NULL to determine whether a column has a NULL value. When this option is set to false, SQL Server allows=NULL as a synonym for IS NULL and <> NULL as a synonym for IS NOT NULL.

Below is the code snippet to demonstrate this behavior.

I have a user table in which Email column is having NULL values,

-- This query returns all the rows where email = null, so column to null comparison works

set
ANSI_NULLS
OFF

GO

select
*
from Users where email =
NULL


 
-- This query doesnt return any row, colum to NULL comparison doesnt work

set
ANSI_NULLS
ON

GO

select
*
from Users where email =
NULL


 
ANSI_PADDING:

When this option is set to ON, string being compared with each other are set to the same length before the comparison take place. When this option is OFF, no padding takes place.

ANSI_WARNING:

When this option is set to ON, errors or warnings are issued when conditions such as division by zero or arithmetic overflow occurs.

CONCAT_NULL_YEILDS_NULL:

When this option is set to ON, concatenating two strings results in a NULL string if either of the string is NULL. When this option is set to OFF, a NULL string treated as an empty (zero-length) string for the purpose of concatenation.