more than one index on a table

madhukp · September 10th, 2004, 08:46 AM

hi all,

I have a table tblCustomers which has about 1000 records. The fields ClientName and Client Points of that table are relevant in this discussion.

I need to list all the clients in alphabetical order in select boxes in more than one page. To make it faster, I have created an index on ClientName. This index is a clustered one. (A stored procedure is used to fetch the list). It is working sufficiently fast.

Then there is a page where I need to list the clients in the descending order of client points. After I created index on ClientName, this listing is fairly slow. I can create another index on ClientPoints (number type). But how can I specify which index to apply in the query in stored procedure ?

I need to apply the first index when I want to fetch Client list in their alphabetical order and the second one when I want to fetch them in the descending order of ClientPoints. May I know how this is possible ?

Thanks

Madhu

happygv · September 10th, 2004, 08:54 AM

If I am right, you should have created a non-clustered index on ClientName, and another on Client Points. IMO, that would have served better.

Hope that helps.
CheerS!

_________________________
- Vijay G
Strive for Perfection

happygv · September 10th, 2004, 09:12 AM

Not sure which version you use. You should be able to do that using WITH INDEX

Code:

SELECT sum(col5) as SumVal FROM Table1 WITH (index('IDX_Col1')) WHERE Col1='something'

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

madhukp · September 11th, 2004, 05:46 AM

Hello Vijay,

This is working correctly.

Thanks a lot.