|
|
Posted by Lyle Fairfield on 04/22/06 13:13
FROM BOL:
A value of NULL indicates that the value is unknown. A value of NULL is
different from an empty or zero value. No two null values are equal.
Comparisons between two null values, or between a NULL and any other
value, return unknown because the value of each NULL is unknown.
Null values generally indicate data that is unknown, not applicable, or
that the data will be added later. For example, a customer's middle
initial may not be known at the time the customer places an order.
Following is information about nulls:
To test for null values in a query, use IS NULL or IS NOT NULL in the
WHERE clause.
When query results are viewed in SQL Server Management Studio Code
editor, null values are shown as (null) in the result set.
Null values can be inserted into a column by explicitly stating NULL in
an INSERT or UPDATE statement, by leaving a column out of an INSERT
statement, or when adding a new column to an existing table by using
the ALTER TABLE statement.
Null values cannot be used for information that is required to
distinguish one row in a table from another row in a table, for
example, foreign or primary keys.
In program code, you can check for null values so that certain
calculations are performed only on rows with valid, or not NULL, data.
For example, a report can print the social security column only if
there is data that is not NULL in the column. Removing null values when
you are performing calculations can be important, because certain
calculations, such as an average, can be inaccurate if NULL columns are
included.
If it is likely that null values are stored in your data and you do not
want null values appearing in your data, you should create queries and
data-modification statements that either remove NULLs or transform them
into some other value.
Important:
To minimize maintenance and possible effects on existing queries or
reports, you should minimize the use of null values. Plan your queries
and data-modification statements so that null values have minimal
effect.
When null values are present in data, logical and comparison operators
can potentially return a third result of UNKNOWN instead of just TRUE
or FALSE. This need for three-valued logic is a source of many
application errors. These tables outline the effect of introducing null
comparisons.
-------
I think that null should not be referred to as a value, in the same way
that celibacy should not be referred to as sex.
In addition, the statements:
"A value of NULL is different from an empty or zero value."
and
"you should create queries and data-modification statements that
either ... or transform them into some other value."
conflict.
Navigation:
[Reply to this message]
|