One of my multi-step queries makes use of optional filters. Rather than using dynamic sql or if statements, I am attempting to use
option(recompile) to make the filtered step choose appropriate indexes regardless of which optional filters are provided. This did not work once I tried to store the results of the sub-expression.
I was able to reproduce this in SSMS as follows:
declare @UID bigint = 100 --Query A: Uses index on UID select count(1) from Entries where (@UID is null or UID = @UID) option(recompile); --Query B: Does an index scan (much slower) select @cnt = count(1) from Entries where (@UID is null or UID = @UID) option(recompile); --Query C: Uses index on UID select @cnt = count(1) from Entries where (UID = @UID);
My goal is to make query B use an index on
@UID is not null. I could accomplish this by conditionally replacing it with Query C, but my preference is to avoid that, as it strikes me as overly verbose. I can't use Query A, since I want to store the intermediate result for use in the next query within a stored procedure.
I did try selecting into a temp table and I tried using
nullif, but these approaches still did a scan rather than a seek.