Link to home
Start Free TrialLog in
Avatar of Faiga Diegel
Faiga DiegelFlag for United States of America

asked on

Constant Scan

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!


ASKER CERTIFIED SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Faiga Diegel

ASKER

You're great! Thanks!