Jess,

Rushmore is a bit mapping query optimization technology. It uses indexes to select records based on a FOR or WHERE clause. A filtered index only contains references to the records that meet the filter. When you set order to a filtered index there is nothing to be optimized as Fox just walks the index tree. Rushmore only affects FOR and WHERE clauses. It uses indexes to optimize a for or where criteria. If an index exists and has a FOR condition on it, Rushmore will NOT use that index.

Fitlered indexes are faster than select because select has to go and get records, but the index does not. Also indexes are set and permanent, while for and where clasues can be dynamic, you get certain recors this time and other records the next time.

If you want a fair test of SELECT against a filtered index try timing this;

SELECT Customer
INDEX ON Name FOR State = "CA"
BROWSE

versus this

SELECT * FROM Customer WHERE State = "NY"

Then try it with an index on State without the for clause. See what the four times are.

If one can predict what then filters will need to be at runtime with a strong confidence that those criteria won't change, then use filtered indexes. But if the users want ad hoc querying capabilities then flitered indexes carry no advantage at all.

Also, if you use SELECT to get ther ecords then the whole selection criteria is right there in the select statement, if you use fiultered indexes you need to know the index filter and the code you are debuggin to know exactly what is being selected. To modify the selection criteria you need to first insure that the index is not being used anywhere else and then change the index, with a select you just change the select.