Larry,
In your case I would probably add index on the fly and then delete it. It could be temporary tag or idx. Unfortunately, this would require exclusive access to the table, which I'm not sure is an option...
Working with Views, as Hilmar suggested, has lots of benefits... If it's possible to switch, you better switch...
In our case, unfortunately, our own framework is not really designed to work with views, so in most cases I work with tables directly, though I have few applications based on views. And also in my case the filter expression in a view was predefined DedupeFlag="P", so it was easy to switch from filter idea to filtered index idea (suggested, BTW, by my colleague - I was thinking about using views, but it would mean, I have to re-design this application).
>Hi Nadya -
>You are correct, I would love to do this, but we have utility tool that rebuilds the database container,Step 1 removes tables then deletes all indexes, then rebuilds indexes, then finally adds them back to empty container. When we tried to perform the rebuild of the index including a filetr we had problems. Primarily due to the fact that the expression in the filter is something like:
>trannumber=m.Ptrannumber , m.Ptrannumber is unknowen or is not initialized at this point.
>
>Your suggestion is the best and the fastest, not only as the fix but performance related as well. It the error we get. We are looking how to address the issue, and if we can using filter in index, its a "HOME RUN"
>
>As always, your input is great.
>
>Larry
If it's not broken, fix it until it is.
My Blog