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

How I can scan all rows of the datasource?

How I can scan all rows of the datasource?
How I can move at the first row of the datasource?
How I can move at the next row, the last, the previous...
It's the reporting designer of Visual Basic .Net.
I would like to scan all rows to find an element or to do an operation, ...
Thanks.

Huh? I didn't get the point.
Mira, si eres de un país de habla hispana escribe tu pregunta en espa?ol; tal vez pueda ayudarte.

|||Reporting Services is not a programmatic / cursor method of accessing data. The rows of the query results are processed by evaluating the expressions in the report definition. Your operations are included in these expressions (e.g. =Fields!A.Value - Fields!B.Value).