sql server - How to reach an index-seek on nullable columns? -

let's assume following code:

create table foo (v1 int null, v2 int not null) create index nifoov1v2 on foo(v1, v2) declare @searchforv1 int = null declare @searchforv2 int = 0 

how can write query uses index find rows specified value of v1 , v2?

basically, there index , should possible find rows single seek. whatever try, sqlserver never use both columns of index.

e.g. tried following:

select * foo with(forceseek) ((v1 null , @searchforv1 null) or (v1 not null , @searchforv1 not null , @searchforv1 = v1)) , v2 = @searchforv2 

in execution-plan, executes 2 independent index-seeks v1 (one null , 1 non-null) , applies filter v2 after this. in case, first index-seeks return 100.000 rows scanned v2 reduce result 100 rows. not efficient.

another way of solving might if-else-construct, write 2 independent queries depending on whether @searchforv1 null or not null. seems awkward...

any other options?

well, turns out sqlserver bit stupid here.

the issue filter-condition used:

((v1 null , @searchforv1 null) or (v1 not null , @searchforv1 not null , @searchforv1 = v1)) 

if remove both "is not null"-checks, index-seek works fine:

((v1 null , @searchforv1 null) or @searchforv1 = v1) 

while both checks semantically equivalent, sqlserver uses index-seek in latter case. when writing code, added both "is not null"-checks on safe side, since unknown-results come comparing null-value non-null-value cause problems when negating whole thing.

thank's dan making me check this.