June 15, 2012 at 6:29 pm
Hello Everyone
I was hoping that someone may be able to understand the IN clause a little more. I know what it can do, and what you can do with it. What I was wondering is how the engine handles the data withing the IN clause. So for example. WHERE ColorID IN(34,76,1,245,567,409,342,98,55,4) How would SQL engine search thru the table? Does the engine make 10 table scans? Or only a single table scan to find all the data? What is the ColorID column is not indexed? Does the engine make multiple scans or a single scan?
Thank You in advance for your help.
Andrew SQLDBA
June 15, 2012 at 7:28 pm
If the column isn't indexed, then a single scan will be performed. If the column exists in a NC index, but isn't the leading column in that index, then the entire NC index will be scanned once to get all the rows (since that's narrower than scanning the main table). If there is no suitable NC index, then the clustered index (if one exists) or heap table will be scanned, again just once only.
If the column is the leading column in an index, then individual index seeks will be done for each value.
From AdventureWorks:
1. No index on e.Title
================
select e.EmployeeID
from HumanResources.Employee e
where e.Title in ('Engineering Manager','Design Engineer')
(4 row(s) affected)
Table 'Employee'. Scan count 1, logical reads 9, physical reads 0 -- Scans clustered index
2. NC index on e.(Gender, Title)
========================
select e.EmployeeID
from HumanResources.Employee e
where e.Title in ('Engineering Manager','Design Engineer')
(4 row(s) affected)
Table 'Employee'. Scan count 1, logical reads 5, physical reads 0 -- Scans NC index
3. NC index on e.(Title)
========================
select e.EmployeeID
from HumanResources.Employee e
where e.Title in ('Engineering Manager','Design Engineer')
(4 row(s) affected)
Table 'Employee'. Scan count 2, logical reads 4, physical reads 0 -- Seeks twice on NC index
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply