Clustered Indexes

Hi -

I am working on a program that does a great deal of searching and sorting
through very large recordsets. Currently, the performance is just ok. I
am hoping to do better than that and one way of doing so that I have been
told about is through the use of clustered indexes.

Now, after doing some reading about clustered indexes I am thinking that
this might be a really good approach. I am thinking about placing multiple
fields within the clustered index but have been told that that approach will
ultimately be counter productive.

To clear up the confusion can someone please provide a brief overview of
the best/worst uses for a clustered index?

Many thanks

Larry

Clustered indexes are great for data that undergoes very little change. An
example of this would be a data base of state names and abbreviations.

They actually rearrange the data in the order of the index, so when using
multiple columns, be sure they are in the order you want to see your data.
For example a Driver's License database. If you put DL # first, then that
is the primary order and since DL #s are unique, any further clustering is
unnecessary. If, however, you want to look up people by name, then putting
first name followed by last name in a clustered index would give you all the
people of the same first name in a group and if they have the same last
name, those will be then grouped as well.

John Jones
John Jones
John Smith
etc.

If you add DL #s to the end of the cluster, then they will be filtered by
that order last.

John Jones 35578
John Jones 43269
John Smith 35577

Of course, middle names/initials can contribute to the mix.
John A. Jones 21906
John A. Jones 43269
John A. Jones 86349
John C. Jones 35578
John B. Smith 35577

Note that the example is just that as a name search on names in a DL
database would not be very productive. You still have to be sure you get
the right one. Of course, other identifiers would be helpful, like address
(but they change), SSN, etc.

"Larry Hunter" <Hunterlmc@gateway.net> wrote in message
news:38d266ed$1@news.devx.com...
>
>
> Hi -
>
> I am working on a program that does a great deal of searching and sorting
> through very large recordsets. Currently, the performance is just ok. I
> am hoping to do better than that and one way of doing so that I have been
> told about is through the use of clustered indexes.
>
> Now, after doing some reading about clustered indexes I am thinking that
> this might be a really good approach. I am thinking about placing
multiple
> fields within the clustered index but have been told that that approach
will
> ultimately be counter productive.
>
> To clear up the confusion can someone please provide a brief overview of
> the best/worst uses for a clustered index?
>
> Many thanks
>
> Larry

Also, Clustered Indexes are (usually) more cost effective when dealing with
data ranges within queries/sp's such as:

select FirstName, Surname
from Client
where DateOfInput BETWEEN 'Jan 01 1999' AND 'Jan 23 1999'

Make sure that when you create a table, you do not accept MSSQL's default
for the PK to be a clusered index, as this is often not the most cost effective.

Cheers
Robert

"C. E. Buttles" <cebuttle@sprintsvc.net> wrote:
>Clustered indexes are great for data that undergoes very little change.
An
>example of this would be a data base of state names and abbreviations.
>
>They actually rearrange the data in the order of the index, so when using
>multiple columns, be sure they are in the order you want to see your data.
>For example a Driver's License database. If you put DL # first, then that
>is the primary order and since DL #s are unique, any further clustering
is
>unnecessary. If, however, you want to look up people by name, then putting
>first name followed by last name in a clustered index would give you all
the
>people of the same first name in a group and if they have the same last
>name, those will be then grouped as well.
>
>John Jones
>John Jones
>John Smith
>etc.
>
>If you add DL #s to the end of the cluster, then they will be filtered by
>that order last.
>
>John Jones 35578
>John Jones 43269
>John Smith 35577
>
>Of course, middle names/initials can contribute to the mix.
>John A. Jones 21906
>John A. Jones 43269
>John A. Jones 86349
>John C. Jones 35578
>John B. Smith 35577
>
>Note that the example is just that as a name search on names in a DL
>database would not be very productive. You still have to be sure you get
>the right one. Of course, other identifiers would be helpful, like address
>(but they change), SSN, etc.
>
>"Larry Hunter" <Hunterlmc@gateway.net> wrote in message
>news:38d266ed$1@news.devx.com...
>>
>>
>> Hi -
>>
>> I am working on a program that does a great deal of searching and sorting
>> through very large recordsets. Currently, the performance is just ok.
I
>> am hoping to do better than that and one way of doing so that I have been
>> told about is through the use of clustered indexes.
>>
>> Now, after doing some reading about clustered indexes I am thinking that
>> this might be a really good approach. I am thinking about placing
>multiple
>> fields within the clustered index but have been told that that approach
>will
>> ultimately be counter productive.
>>
>> To clear up the confusion can someone please provide a brief overview
of
>> the best/worst uses for a clustered index?
>>
>> Many thanks
>>
>> Larry
>