I'll start with a health warning - please test anything I say as I'm still a novice at this (and dear Experts, please be gentle if you need to shoot...

Well, I think its my server, not my query.

I dropped and re-created my original SP and that's now doing a seek and takes 98% less time.:-P

So, thanks for the replies,...

Thanks for the replies - I've done a little more experimenting to get the simplest query that will still result in a table scan. Here is my SP:

ALTER PROCEDURE [dbo].[FB_EndASPECTSbaseline3]...

Hi Grant,

I don't understand how it could be parameter sniffing - surely having an OPTION (RECOMPILE) at the end should prevent this?

Loved the book on dissecting execution plans, by the...

In this example, there's just the 1 entry in the CASE statement, but I'll check my other queries in case there are more. The cross-server queries that recently broke all...

Thanks for checking that ALZDBA.

I've checked again here and, if I run both from Query Analyser 2000 from a colleague's computer, connected to the SQL2000 server, they also both work...

Thanks for that - I probably should have made pupilID unique, but there are legitimate reasons why a school might try entering a duplicate pupilID (e.g. if a pupil moves...