This may be the most obscure question I ever asked here.
I've got a PreparedStatement running at a customer site that selects only by fields in the primary key. At this site--and nowhere else--it's running for hours because it's deciding to use an index scan, instead of an index seek.
My table has a primary clustered key of KeyA, KeyB and KeyC. And I have a PreparedStatement that says:
SELECT * FROM MyTable where KeyA = ? and KeyB = ? and (KeyC > ? and KeyC < ?)
I'm Profile Tracing the plan, and at that site it's clearly saying "index scan". They have SQL 2000 SP4. I run it here, with SQL 2000, no SP, and it runs in 2 minutes, with "index seek".
I have posted this problem first in the MS SQL site, and they've tried like hell but they don't know what it is either. I'm posting here because of one more clue: At the cust site, I created a stored procedure that did approximately what my PreparedStatement does, and it ran correctly, with index seek. So I'm thinking it could possibly be a problem with PreparedStatement in conjunction with one of the SQL 2000 Service Packs.
If any of you can definitively answer this question, I will personally deliver a large stuffed bunny to your door first thing in the morning.