Using Null Values

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:

  • If a row is inserted into the table but no data value is specified for the nullable column, SQL Server will assign it the value NULL (unless a default value has been specified for that column).
  • A user can type the word "NULL," without quotation marks so that it will not be confused with the character string "NULL."

Creating the Product_Info Table Using NULLs

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.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net