Gail

I think this is on-topic and you seem to know as much (or more) than anyone on the subject of index behavior that I'd like to ask a question that I've posted before but no one has ever had a definitive answer.

I notice that on many queries, depending on the makeup of the WHERE clause, that SQL will be doing Index Scans that no amount of fiddling around seems to help. I see this most often when I'm returning all rows with no WHERE clause at all (which sort of makes sense).

But I can add an artificial filter like 'WHERE primarykey > 0" and that seems to force the compiler into doing an Index Seek on that key. I've wondered about this for years now. Is this really changing the execution of the query in any significant way? Or is it just a 'phantom" effect of some kind?

I've attached two Execution Plan files that were run against the same table to give an example of this behavior.