Null (SQL) - Effect On Index Operation

Effect On Index Operation

Some SQL products do not index keys containing NULL values. For instance, PostgreSQL versions prior to 8.3 did not, with the documentation for a B-tree index stating that

B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators: < ≤ = ≥ >

Constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search. (But note that IS NULL is not equivalent to = and is not indexable.)

In cases where the index enforces uniqueness, NULL values are excluded from the index and uniqueness is not enforced between NULL values. Again, quoting from the PostgreSQL documentation:

When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Null values are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.

This is consistent with the SQL:2003-defined behavior of scalar Null comparisons.

Another method of indexing Nulls involves handling them as not distinct in accordance with the SQL:2003-defined behavior. For example, Microsoft SQL Server documentation states the following:

For indexing purposes, NULL values compare as equal. Therefore, a unique index, or UNIQUE constraint, cannot be created if the key values are NULL in more than one row. Select columns that are defined as NOT NULL when columns for a unique index or unique constraint are chosen.

Both of these indexing strategies are consistent with the SQL:2003-defined behavior of Nulls. Because indexing methodologies are not explicitly defined by the SQL:2003 standard, indexing strategies for Nulls are left entirely to the vendors to design and implement.

Read more about this topic:  Null (SQL)

Famous quotes containing the words effect on, effect, index and/or operation:

    We are such docile creatures, normally, that it takes a virus to jolt us out of life’s routine. A couple of days in a fever bed are, in a sense, health-giving; the change in body temperature, the change in pulse rate, and the change of scene have a restorative effect on the system equal to the hell they raise.
    —E.B. (Elwyn Brooks)

    This is the great truth life has to teach us ... that gratification of our individual desires and expression of our personal preferences without consideration for their effect upon others brings in the end nothing but ruin and devastation.
    Hortense Odlum (1892–?)

    Exile as a mode of genius no longer exists; in place of Joyce we have the fragments of work appearing in Index on Censorship.
    Nadine Gordimer (b. 1923)

    You may read any quantity of books, and you may almost as ignorant as you were at starting, if you don’t have, at the back of your minds, the change for words in definite images which can only be acquired through the operation of your observing faculties on the phenomena of nature.
    Thomas Henry Huxley (1825–95)