Need to get rid of the index scan

Julianv 2017-08-24 07:39:23

Aaron Bertrand 2017-08-24 13:06:58
It can be really tough to avoid a scan with that messy predicate of mixed ANDs and ORs, especially since your OR predicate may or may not be reading the leading key column. How many rows match the primary predicate of fkUserID = @UserID_char? I wonder if you could UNION ALL one query that checks that predicate (which would hopefully lead to a seek), and then a second query that checked AssignedTo (which won't lead to a seek, but could if you created an index on AssignedTo, and it might even make sense to make it filtered if it only suits this query pattern (the filter would be where AssignedTo is not null)).