March 10, 2016 at 3:03 am
Hi,
I have created a table with clustered index for ID column and also created non-clustered index for same column.
Create Table Index_Test (id int,Name varchar(10))
insert into Index_Test select 1,'A'
insert into Index_Test select 2,'B'
insert into Index_Test select 3,'C'
insert into Index_Test select 1,'D'
insert into Index_Test select 2,'E'
insert into Index_Test select 3,'F'
insert into Index_Test select 1,'G'
insert into Index_Test select 1,'H'
Now i'm creating index for above table.,
Create clustered index ind_name on Index_test(id)
Create index ind_name1 on Index_test(id)
Then i'm running below query with Actual execution plan,
select ID from Index_Test where id=1
when i see the execution plan, the index seek is performing through ind_name1 index. But not via ind_name???
May i know what is the reason and how its working???
R's,
Arun
March 10, 2016 at 3:24 am
ind_name1 contains just the ID, which is the only column you're filtering on and the only column you're returning, so it makes sense to use the smallest possible index to retrieve the data.
The clustered index contains all the columns at leaf level, so it's bigger.
Why were you expecting to be using the clustered index instead?
-- Gianluca Sartori
March 10, 2016 at 3:24 am
The nonclustered index is smaller, since it doesn't have the name column in it. Hence for a query that only references the ID column anywhere, the nonclustered index is slightly faster as it's smaller. Hence it's the one 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply