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 Sinclair

Sinclair (Sincl***@discussions.microsoft.com) writes:
Show quoteHide quoteWell, 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.)

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.

Delete and insert data in same transaction!
SQL Jobs Fail but give no error
Optimal storage of a lot of text records
How to save an HTML doc in SQL 2005
moving from Access to SQL 7.0