I have a simple select, something like this:
SELECT Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9
FROM Table1 T1
INNER JOIN Table2 T2 on T1.Column20 = T2.Column1
WHERE T1.Column10 = @Col10
AND T1.Column11 = @Col11
AND T1.Column12 = @Col12
AND T1.Column13 = @Col13
AND T1.Column14 = @Col14
AND ISNULL(T1.Column15) = ISNULL(@Col15,0)
clustered index n T2.column1
nonclustered index on T1.Column20
nonclustered index on T1.Column10
I keep getting Constant Scan. The Execution Plan looks something like:
SELECT (Cost: 0%) <-------------------- Constatn Scan (Cost: 100%)
I notice one thing though, the T1.Column11 pass in value is NULL. If I remove that WHERE condition line (AND T1.Column11 = @Col11), then I will get the usual execution plan of Index Seek, KeyLookup, Nested Loops, etc -- which is pretty accetable.
T1.Column11 is non nullable but somewhere we pass in NULL value but sometimes we pass in some value too.
Why am I getting constant scan with that candition? I read and read and read about constant scan, but couldn't really grasp what was that. I don't think my ISNULL function on my WHERE condition is the culprit to the constant scan, i am sure it's the NULL value pass in to T1.Column11.
Any suggestions and advice are greatly appreciated, Thanks!