Specifies that the fields of a column must contain a value; they cannot be null.
NOT NULL is ANSI SQL:2011 compliant.
The NOT NULL specification for a column ensures that all fields of that column will contain a value. An error is returned if you attempt to insert row data that does not have a value for the column, or has a NULL for the column.
If a NOT NULL phrase is specified for a column in an ALTER TABLE statement, a DEFAULT or a WITH DEFAULT phrase must also be specified unless the table is empty. This ensures that any empty field in that column will be supplied with default values in compliance with the NOT NULL specification.
Recommendation: As a best practice, consider specifying NOT NULL for all columns unless there is a valid reason to allow nulls.
If a column should contain nulls, be sure you understand what NULL means for the column since NULL has many interpretations. In some cases, it is better to define a non-null default value rather than allow nulls. If a non-null default value is specified for the column, consider specifying NOT NULL for the column also.
If a column is nullable (even if no nulls are actually put in the column), every row will have a presence bit for the column to indicate its nullability. This may cause an increase in row size if an additional presence byte is required to accommodate this presence bit. If the column should not have nulls, specifying NOT NULL eliminates the need for the presence bit and potentially saves a byte per row.
Specifying NOT NULL also allows for better optimization of some queries since additional processing to handle nulls, or the potential of nulls in a column, can be avoided.
For more information on the advantages and disadvantages of using nulls, see Database Design.
Example: NOT NULL Phrase
In the following table definition, the FullName column is defined as NOT NULL, thus ensuring that every row in the Members table has a value for FullName.
CREATE TABLE Members( FullName VARCHAR(42) NOT NULL, Status CHAR(6), Phone CHAR(10));