3 4
A null value is an unknown value that we refer to as NULL. The nullability of a column refers to the ability of the column to accept or reject null values. A null value in a column usually indicates that no entry has been made into that column for a particular row of data because the value is either not known, not applicable, not defined, or to be added at a later time. Null values are neither empty values nor 0 values; their true values are unknown—so no two null values are equal.
A nullable column might be needed if information that you need is not available yet—for example, a customer's middle initial. What should the column contain in a record for someone who does not have a middle name and therefore no middle initial? If NULL is allowed in that column, a null value in that record would be true and would make sense—it would let you know that the information is not applicable.
As a general rule, avoid allowing null values. They cause more complexity in queries and updates, and some options, such as primary keys and the IDENTITY property, cannot be used on a nullable column.
MORE INFO
For more information, display the Books Online index entry for Null Values, and then select the topic "Comparison Search Conditions." Also see the section "Adding the IDENTITY Property" later in this chapter.)
An excellent alternative to allowing NULLs in a column is to define a default value for the column. When a value is not specified for a row insert, the default value is entered into the column. (See Chapter 16 for more information about using defaults.) If you do define a column to allow null values, a NULL can be entered into that column in one of two ways:
Returning to our Product_Info table example, let's add the null options to each column definition. If you want a column to allow null values, add NULL after the data type. If you do not want null values allowed, add NOT NULL after the data type. It is a good practice to always specify whether a column should allow null values—except when you are using a user-defined data type that has already been defined with NULL or NOT NULL. Doing this will help get you into the habit of considering what the nullability should be for columns.
MORE INFO
To learn what the default nullability will be when NULL or NOT NULL is not specified, see the "CREATE TABLE" topic in Books Online and scroll down to the section "Nullability Rules Within a Table Definition." Explicitly specifying NULL or NOT NULL takes precedence over these rules.
In our Product_Info table example, let's allow only the product description column to accept null values. We do not have to specify nullability for the brand_type data type, as its nullability was defined (as NOT NULL) when we created the user-defined data type. The new CREATE TABLE statement will look like this:
USE MyDB GO DROP TABLE Product_Info GO CREATE TABLE Product_Info ( Product_ID smallint NOT NULL, Product_Name char(20) NOT NULL, Description char(30) NULL, Price smallmoney NOT NULL, Brand_ID brand_type ) GO
Now, if a product description is not specified but all four other values are, when you enter a product's data, the new row will be inserted in the table with a NULL for the Description column entry. You must enter values for the four other columns Product_ID, Product_Name, and Price, Brand_ID, which do not accept NULLs. If one of these columns is empty, the attempted entry of the new row will not succeed.