Nullability Rules Within a Table Definition

The nullability of a column determines whether or not that column can allow a null value (NULL) as the data in that column. NULL is not 0 or blank. It means no entry has been made or an explicit NULL was supplied, and it usually implies that the value is either unknown or not applicable.

When creating or altering a table with the CREATE TABLE or ALTER TABLE statements, database and session settings will influence and possibly override the nullability of the datatype used in a column definition. It is recommended that you always explicitly define a column as NULL or NOT NULL or, if using a user-defined datatype, allow the column to use the datatype's default nullability.

When not explicitly specified, column nullability follows these rules:

1. If the column is defined with a user-defined datatype:
   • SQL Server will use the nullability specified when the datatype was created. Use the sp_help system stored procedure to get the datatype's default nullability.
2. If the column is defined with a system-supplied datatype:
   • If the system-supplied datatype has only one option, it will take precedence. Currently, the bit and timestamp datatypes can be defined only as NOT NULL.
   • If any session settings are ON (turned on with the SET), then:

     ·If ANSI_NULL_DFLT_ON is ON, NULL will be assigned.

     ·If ANSI_NULL_DFLT_OFF is ON, NOT NULL will be assigned.

   • If any database settings are configured (changed with the sp_dboption System Stored Procedure), then:

     ·If 'ANSI null default' is TRUE, NULL will be assigned.

     ·If 'ANSI null default' is FALSE, NOT NULL will be assigned.

3. When not explicitly defined (neither of the ANSI_NULL_DFLT options are set), the session has not been changed and the database is set to the default ('ANSI null default' is FALSE), then the SQL Server default of NOT NULL will be assigned.