Index Question

Hello,
I have a 500,000 record table with the primary key being a bigint
identity column (clustered). I have another column (smallint) that it is not
unique and only has 20 possible values, this column is indexed in ascending
order.
When I do a select statement in the query analyzer filtering by the
smallint column, I notice that in the execution plan the index of this
column is not being used, it does only a clustered scan. Is it because the
smallint column is not unique? Other reason?
Thanks in advance...
Jose.
Most likely because of the low selectivity on that index. With a very low
number of unique values compared to the number of rows in the table, doing
an index seek is probably more expensive than a table (or clustered index)
scan. This is particularly true if the query is not "covered" by the index
in question. You could verify this by using an index hint in your query and
looking at the execution plan compared to the execution plan for the full
scan.
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a 500,000 record table with the primary key being a bigint
> identity column (clustered). I have another column (smallint) that it is
not
> unique and only has 20 possible values, this column is indexed in
ascending
> order.
> When I do a select statement in the query analyzer filtering by the
> smallint column, I notice that in the execution plan the index of this
> column is not being used, it does only a clustered scan. Is it because the
> smallint column is not unique? Other reason?
> Thanks in advance...
> Jose.
>
|||Yes I did it and the smallint index turned out with 0% cost.
Thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23bxFqwULEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Most likely because of the low selectivity on that index. With a very low
> number of unique values compared to the number of rows in the table, doing
> an index seek is probably more expensive than a table (or clustered index)
> scan. This is particularly true if the query is not "covered" by the
index
> in question. You could verify this by using an index hint in your query
and[vbcol=seagreen]
> looking at the execution plan compared to the execution plan for the full
> scan.
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> not
> ascending
the
>
sql