Local Variables and BETWEEN clause

Question

Local Variables and BETWEEN clause

brian118

Hall of Fame

Points: 3506
More actions
August 4, 2014 at 6:19 am

#291676

Hi
I have a situation which is puzzling me!
Basically I have a simple Select statement and I'm passing the value of the WHERE clause as a local variable. As expected, the optimiser is not using the Index (there is a specific index which can be used) but instead opted for a table scan. I'm fine with this and it's expected since the local variable value is not known at execution time.
However if I run the Same Select statement however this time using local variable but instead of inequality(>=) I do a BETWEEN, even though I'm using local variables, an index seek was performed!
The table has 400m rows and I'm selecting 300K.
-- Index is not used
Select Min(Id)
From Table
Where Id >= @LocalVariable1
-- Index is used
Select Min(Id)
From Table
Where Id Between @LocalVariable1 And @LocalVariable2 (@LocalVariable2 is the maxID of the table)
I was under the impression that when local variables are used, irrespective of the values passed, a 30% selectivity is done
Regards
Brian
Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

There's nothing about local variables which prevent index usage.

That 30% is the worst possible case for a guess by the optimiser, not the norm. If it can manage a better estimate, and it often can using the density, then it may be that the estimate will be low enough that a seek on a non-covering index is chosen.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

brian118 Hall of Fame Points: 3506 More actions · Answer 2

GilaMonster (8/4/2014)
There's nothing about local variables which prevent index usage.
That 30% is the worst possible case for a guess by the optimiser, not the norm. If it can manage a better estimate, and it often can using the density, then it may be that the estimate will be low enough that a seek on a non-covering index is chosen.

Hi Gail

Thanks for your response. OK I see, then by using the Density information, the optimiser can still opt for an Index seek even though local variables are used. I was under the impression that when using local variables, the optimiser will just go for a table scan. That explains it then.

Thanks

Regards
Brian Ellul
----------------------------------------------------------------------------------
Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
- Albert Einstein -