asked
Index Scan vs Index Seek
Create Table TableT ( id int, ch char(1), na varchar(5), Flag char(1) ) go insert into TableT select 2,'A','jack','Y' union all select 5,'b','amy','N' union all select 1,'$','adams','N' union all select 3,'*','anna','Y' union all select 7,'@','rose','N' union all select 4,'&','smith','Y' union all select 6,'!','sue','Y' select * from TableT Create Nonclustered index NC_TableT on TableT(id,ch,na) select na from TableT where ch = '!' --Index Scan select na from TableT where id = 6 and ch = '!'--Index Seek select na from TableT where ch = '!' and id = 6 --Index Seek select na from TableT where na = 'sue' and id = 6 and ch = '!'--Index Seek Here On all The Above Queries My First Query Is performing Index Scan Remaining all Are Index Seek.May I know The Reason Why?
Your index is a composite index, made up of the three fields. Because the first `select` is only searching on the `ch` field, which isn't the first field in the index definition, the engine is having to scan the whole index. The other selects make reference to the `id` field, which is the first field in the composite index, and therefore the engine can seek out the appropriate entries. Something like that, anyway. I'm not an indexing expert...
Could have fooled me. That's exactly it. While the column ch is in the index, it's not the leading edge (first column) so storage is done by the column order with id being first. So just searching on ch requires a scan.
Since your index key is in the order (ID,CH, NA), anything that searches ID,CH or ID,NA (in any order) may result in an index seek (only if your query result in few rows, if most of the rows needs to be returned this may result in index scan). This is because with your index structure, index pages are sorted with ID CH and NA so it can navigate the BTree based on the Key ID only. Moreover stats are maintained only for first column of the index.
16 People are following this question.
