Does index helps in BETWEEN clause in SQL Server?
If I have table with 20000 rows and query is:
select *
from employee
where empid between 10001 and 20000
Asked
Active
Viewed 84 times
0
-
Check the execution plan. (And, the answer is probably yes.) – jarlh Sep 07 '17 at 07:36
-
Anything in where clause index will help. – Rakib Sep 07 '17 at 07:40
1 Answers
7
Yes. This is sargable.
It can do a range seek on an index with leading column empid. Navigating the B-tree to find the first row >= 10001 and then reading all rows in key order until the end of the range is reached.
You might not get this plan unless the index is covering though. Your query has select * so an index that only contains empid may potentially need to do 10,000 lookups to get the missing columns.
If empid is the primary key of employee then by default it will be the clustered index key (unless you specified otherwise) so this will automatically be covering and you should expect to see a clustered index seek.
Martin Smith
- 438,706
- 87
- 741
- 845