NULL meansnull value; it's not the same as zero or blank. NULL means that the users made no entry and often implies missing or otherwise undefined data. Because you can easily search for NULLs, people frequently use them to flag missing data. Nullability refers to the ability of a column to accept NULL values. Among the settings that can affect nullability are the table definition (if you have explicitly defined columns as NULL or NOT NULL), session settings (if they're turned ON), and database-specific settings (if you've set them with sp_dboption).
NULL and nullability can present several problems. Microsoft's SQL Server defaults to OFF (NOT NULL), but the ANSI SQL standard is ON (NULL). For ANSI compatibility, setting ANSI_NULLS to ON changes the database default nullability to NULL. All user-defined datatypes or columns not explicitly defined as NOT NULL during a CREATE TABLE or ALTER TABLE statement default to allowing NULLs. However, session settings override the default database setting for the ANSI NULL default. SQL Server 6.5 resolves ANSI NULL settings with stored procedures during query execution, not query creation. SQL Server 7.0, however, will perform ANSI_NULLS bindings when you create stored procedures, not when you execute them.
Some applications depend on ANSI_NULLS set to ON, and some depend on ANSI_NULLS set to OFF. When you upgrade databases from SQL Server 6.x to SQL Server 7.0, choose the ANSI_NULLS option based on your application's expected behavior.
