ANSI_NULL_DEFAULT { ON | OFF } :
When you dontdon't specify ANSI_NULL_DEFAULTthe nullability of a column in CREATE TABLE or ALTER TABLE statements, this database option will determine the default value, NULL or NOT NULL for a column or CLR user-defined type.
When SET to ON, the DEFAULT value is NULL. When SET to OFF, the DEFAULT value is NOT NULL.
Connection-level settings that are set by using the SET statement override the default database-level setting for ANSI_NULL_DEFAULT. By default, ODBC and OLE DB clients issue a connection-level SET statement setting ANSI_NULL_DEFAULT to ON for the session when connecting to an instance of SQL Server.
ANSI_NULLS { ON | OFF }:
ANSI_NULL should be ON. This will be a default behavior in future versions of SQL Server.
When ANSI_NULLS is set to ON then NULLs follow the ISO compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators.
When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When ANSI_NULLS is set to OFF then a comparison of NULL=NULL or NULL<>NULL returns TRUE or FALSE respectively, instead of an unknown answer NULL. It treats NULL into a value of its own.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL.
Always remember that session level setting will override database level setting for the above SET options.
Reference : SET ANSI_NULL_DFLT_ON and SET ANSI_NULLS
