NULLs and Nullability

NULL meansnull value; it's not the same as zero or blank. NULL means that the users made no entry and often implies missingor otherwise undefined data. Because you can easily search for NULLs,people frequently use them to flag missing data. Nullabilityrefers to the ability of a column to accept NULL values. Among thesettings 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 proceduresduring query execution, not query creation. SQL Server 7.0, however, willperform ANSI_NULLS bindings when you create stored procedures, not when youexecute them.

Some applications depend on ANSI_NULLS set to ON, and some depend onANSI_NULLS set to OFF. When you upgrade databases from SQL Server 6.x toSQL Server 7.0, choose the ANSI_NULLS option based on your application'sexpected behavior.