The ANSI_NULLS option controls both database default nullability and comparisons against null values. When upgrading Microsoft® SQL Server™ version 6.x to SQL Server version 7.0, you must set the ANSI_NULLS option to ON or OFF.
When the SQL Server Upgrade Wizard creates the SQL Server 7.0 database tables, the database default nullability determined by the ANSI_NULLS option is not an issue. All columns are explicitly qualified as NULL or NOT NULL based on their status in SQL Server 6.x.
The ANSI_NULLS option is important with regard to comparisons against null values, when the SQL Server Upgrade Wizard creates the SQL Server 7.0 database objects. With ANSI_NULLS set to ON, the comparison operators EQUAL (=) and NOT EQUAL (<>) always return NULL when one of its arguments is NULL. With ANSI_NULLS set to OFF, these operators will return TRUE or FALSE depending on whether both arguments are NULL.
In SQL Server 6.x, the ANSI_NULLS option in objects, such as stored procedures and triggers, is resolved during query execution time. In SQL Server 7.0, the ANSI_NULLS option is resolved when the object is created. You must choose the ANSI_NULLS option setting you want for all objects in the databases you are upgrading. The SQL Server Upgrade Wizard then creates all database objects using this ANSI_NULLS setting.
| SET ANSI_NULL_DFLT_ON | SET ANSI_NULLS |