optimized indexing

Hi,
I would like to consider optimized indexing on the table which really does
not exist in my database, but will serve as an example for creating indexes.
Suppose that there is a table with following fields:
- family name,
- first name,
- dateofbirth,
- street,
- home number,
- city
Suppose also that there is an searching engine that is querying our example
table with statistics by these parameters:
- family name and first name - 50%,
- family name only - 20%,
- first name only - 15%,
- family name and street - 15%
Is it Ok (because of the best querying) to create following indexes:
- IX1 (family name, first name),
- IX2 (first name)
- IX3 (family name, street)
or there are other options?
Best Regards from SinclairSinclair (Sinclair@.discussions.microsoft.com) writes:
> I would like to consider optimized indexing on the table which really
> does not exist in my database, but will serve as an example for creating
> indexes.
> Suppose that there is a table with following fields:
> - family name,
> - first name,
> - dateofbirth,
> - street,
> - home number,
> - city
> Suppose also that there is an searching engine that is querying our
> example table with statistics by these parameters:
> - family name and first name - 50%,
> - family name only - 20%,
> - first name only - 15%,
> - family name and street - 15%
> Is it Ok (because of the best querying) to create following indexes:
> - IX1 (family name, first name),
> - IX2 (first name)
> - IX3 (family name, street)
> or there are other options?
Well, the above is not bad, but it fails to take in account the question is
which index to make the clustered index.
Since family name is popular, that is my pick for the clustered index. This
results in:
ClusterIX - (family naee)
NC_IX1 - (first name)
NC_IX2 - (street)
You may be missing family name from the non-clustered indexes, but it is
not. The clustrered index works as row locator in the non-clustered indexes,
so all columns in the clustered index appears there as well. (It would not
be wrong to explicitly include family name in these indexes.)

> I apologize to ask again. Related to the example in previous question
> about optimized indexing, should there be changes in proposed indexes,
> if the field [family name] takes part in INNER JOIN with some other
> table (Foreign key)?
With knowing only this, it is difficult to say whether it would matter
at all. But if it does, it is likely to enfoce the idea that family
name should be the clustered index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx