Do you have any index on the hcs.PatientOrder table for column PatientVisit_ID? That might help out, as a variable makes use of this field. Aside from that the optimiser will choose the best plan based in part at least on the number of rows the subquery will return . If a bunch might as well scan the whole hcs.AdminObservation table. If a few then having the index that Luis suggested might give you a seek instead. There is not a real reason to be insisting that it be a certain way once you have all your index pieces in place. The optimiser handles the rest.