mikesigsworth (12/2/2010)
Well the find query that I am currently optimizing is paged so it returns at most 20 rows on a 2.5million row table. So I guess by using paging I can reduce the result set to the required size in order to use the index.
That's well within the range where the lookups will be done.
The other point is, have you tested and does SQL use the index? If you haven't tested, do so. You cannot put indexes onto tables based on what you think will happen. You have to test and make sure that what you expect to happen really will.
You are a king among men.
Err, I'll take that as figurative, not literal 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability