Faiga Diegel
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!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER