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:
“The use of symbols has a certain power of emancipation and exhilaration for all men. We seem to be touched by a wand, which makes us dance and run about happily, like children. We are like persons who come out of a cave or cellar into the open air. This is the effect on us of tropes, fables, oracles, and all poetic forms. Poets are thus liberating gods.”
—Ralph Waldo Emerson (1803–1882)
“Nothing could his enemies do but it rebounded to his infinite advantage,—that is, to the advantage of his cause.... No theatrical manager could have arranged things so wisely to give effect to his behavior and words. And who, think you, was the manager? Who placed the slave-woman and her child, whom he stooped to kiss for a symbol, between his prison and the gallows?”
—Henry David Thoreau (1817–1862)
“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)
“It is critical vision alone which can mitigate the unimpeded operation of the automatic.”
—Marshall McLuhan (1911–1980)