How many rows before optimizer will use index

Hi
2 questions for you Gentlemen ;-)
1:
I think I have heared that SQL Server optimizer (always ?)
will use a table scan, if the row count of a table is
under a certain value even if there exist an - othervise
appropriate - index, simply because this is considered
faster.
How many rows do a table need to have before indexes are
considered usefull - does a roughly value exist?
(I think I read the number 8000 rows once)
Is there any differences between indextypes (CL, NCL,
single/multicolumn...)in the minimum "required" row count
2:
In a table A with many rows a column colA1 exists (with
a "code type" value), referencing table B with few rows
containing the list of codes.
Though a FK (and sometimes used in queries, joins etc.)
woundn't it be superfluous to index the FK column colA1,
because of the relative bad/low selectivity of the
different domain values (both if an fairly even spread of
the different few codes are assumed or if one of the codes
are in majority).
kind regards
Jakob Persson
Denmarkactually,
SQL Server will almost always try to use an index unless
there are atleast 6-12 rows, and the plan involves a NC
index and bookmark lookups
see:
http://www.sql-server-
performance.com/jc_sql_server_quantative_analysis1.asp
for more info
2. if you don't expect a plan to use the index, it may not
be necessary. i am assuming you do not delete from table B.
>--Original Message--
>Hi
>2 questions for you Gentlemen ;-)
>1:
>I think I have heared that SQL Server optimizer
(always ?)
>will use a table scan, if the row count of a table is
>under a certain value even if there exist an - othervise
>appropriate - index, simply because this is considered
>faster.
>How many rows do a table need to have before indexes are
>considered usefull - does a roughly value exist?
>(I think I read the number 8000 rows once)
>Is there any differences between indextypes (CL, NCL,
>single/multicolumn...)in the minimum "required" row count
>2:
>In a table A with many rows a column colA1 exists (with
>a "code type" value), referencing table B with few rows
>containing the list of codes.
>Though a FK (and sometimes used in queries, joins etc.)
>woundn't it be superfluous to index the FK column colA1,
>because of the relative bad/low selectivity of the
>different domain values (both if an fairly even spread of
>the different few codes are assumed or if one of the
codes
>are in majority).
>kind regards
>Jakob Persson
>Denmark
>.
>|||Jakob Persson wrote:
<snip>
> 2:
> In a table A with many rows a column colA1 exists (with
> a "code type" value), referencing table B with few rows
> containing the list of codes.
> Though a FK (and sometimes used in queries, joins etc.)
> woundn't it be superfluous to index the FK column colA1,
> because of the relative bad/low selectivity of the
> different domain values (both if an fairly even spread of
> the different few codes are assumed or if one of the codes
> are in majority).
This is only true if the values of colA1 are evenly distributed. If the
data distribution is skewed (for example, many 'A' but few 'B'), then
the index can still be beneficial when querying on 'B'.
And (as Joe mentioned), the index is also useful when deleting rows from
the referenced table.
Gert-Jan