Nulls in indexes

Hi,
If I have a column that allows NULLs and the majority of the queries that
run against the table look for rows when that column is NULL.
Would an index increase the performance of the query?
Thanks,
--AML--Possibly. NULL, seen from am index perspective, is just another value. But i
n the end, usefulness of
an index here is determined by selectivity (how many Null's do you have comp
ared to number of rows),
the query in whole (perhaps there are better indexes) and stuff like that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aaron M. Lowe" <alowe@.uic.edu> wrote in message news:e4TTDQ3PFHA.1236@.TK2MSFTNGP14.phx.gbl
..
> Hi,
> If I have a column that allows NULLs and the majority of the queries that
run against the table
> look for rows when that column is NULL.
> Would an index increase the performance of the query?
> Thanks,
> --AML--
>|||It might, if the majority of the rows are not NULL. In this case, the index
will be highly selective with regard to NULLs and a s will be possible
when you query for Col IS NULL. On the other hand, if most of the rows have
a NULL for that column, the index will not be selective and a scan will most
likely be used...
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Aaron M. Lowe" <alowe@.uic.edu> wrote in message
news:e4TTDQ3PFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> If I have a column that allows NULLs and the majority of the queries that
> run against the table look for rows when that column is NULL.
> Would an index increase the performance of the query?
> Thanks,
> --AML--
>|||In addition to the other responses: this index may also be useful if it
is a covering index or clustered index.
Gert-Jan
"Aaron M. Lowe" wrote:
> Hi,
> If I have a column that allows NULLs and the majority of the queries that
> run against the table look for rows when that column is NULL.
> Would an index increase the performance of the query?
> Thanks,
> --AML--