>>I know of a few tricks how to prevent an index from being used
>Please share! (If they can be applied dynamically depending on input
>values)

I was just thinking about common things like adding +0 or changing an inner join to a left join, but as written, these tricks can only prevent indexes from being used, and that is not something that you want to do.

>>you're not going to get equally good performance as you would if your
>>WHERE clause were determined dynamically.
>I can't do that from within a stored procedure, can I?

I don't use it myself, but I would expect [FOR] EXECUTE STATEMENT with a dynamically generated statement to be able to be able to use indexes.

>>You may end up choosing to do things in a way that users 'normally'
>>will be content with.
>Problem for me here is, use of each is pretty much 50/50, not 99 to 1.

Well, if criteria for fields chosen for indexes only are selective in 50% of the cases, then such an approach would only be appreciated/appropriate half the time...

Good luck in digging into EXECUTE statement,
Set