Allowing Null Values
The nullability of a column determines whether the rows in the table can contain a null value for that column. A null value, or NULL, is different from zero (0), blank, or a zero-length character string such as "". NULL means that no entry has been made. The presence of NULL typically implies that the value is either unknown or undefined. For example, a null value in the SellEndDate column of the Production.Product table of the AdventureWorks database does not mean that the item has no selling end date. NULL means that the date is unknown or has not been set.
We recommend that you avoid permitting null values, because null values incur more complexity in queries and updates; and also because there are other column options, such as PRIMARY KEY contraints, that cannot be used with nullable columns.
If a row is inserted but no value is included for a column that allows for null values, the SQL Server 2005 Database Engine supplies the value NULL, unless a DEFAULT definition or object exists. A column defined with the keyword NULL also accepts an explicit entry of NULL from the user, regardless of what data type it is or if it has a default associated with it. The value NULL should not be added within quotation marks because it will be interpreted as the character string ''NULL'', instead of the null value.
Specifying a column as not permitting null values can help maintain data integrity by guaranteeing that a column in a row always contains data. If null values are not allowed, a user that enters data in the table must enter a value in the column or else the table row cannot be accepted into the database.
Columns defined with a PRIMARY KEY constraint or IDENTITY property cannot allow null values.