SET ANSI_NULLS at the database level versus setting it at the table level
ANSI_NULL_DEFAULT { ON | OFF } :

When you dont specify ANSI_NULL_DEFAULT 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 (&lt;>) 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 &lt;> 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&lt;&gt;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 (&lt;>) 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 &lt;> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name &lt;> 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