20.03.2009

How Clustered index affects non-clustered indexes

While i was reading Pinal Dave’s blog which is the one of  the best blog about SQL Server , i read Effect of clustered index over non-clustered index. This post is variant of it.
A heap is a table without a clustered index. Creating non-clustered indexes on a heap does not provide  benefits of using indexes.
Create a heap

if exists(select * from sys.tables where name = 'tbl_tags')
drop table tbl_tags
go
create table tbl_tags
(
TagId int not null ,
Name varchar(255)
)
go



Insert some rows



declare @i int = 1000

while @i > 0
begin
insert into tbl_tags(TagId,Name) values (@i,cast(@i as varchar(4)))
set @i -=1
end





Retrieve Row by filtering Name column




select * from tbl_tags where Name =  '1' 





Execution Plan is the picture below


image


Secondly create non-clustered index on Name column



create nonclustered index IX_tbl_Tags on tbl_Tags(Name)
Again retrieve rows by filtering Name column and look execution plan


image 







See it again Optimizer chooses table scan method to retrieve rows

Now create a clustered index on Id column,retrieve rows by filtering Name column and look execution  plan


   create unique clustered index PK_tbl_tags on tbl_Tags(TagId)
go
select * from tbl_tags where Name = '1'
image


As a conclusion from our observations every
non-clustered index needs
clustered index internally.

2 yorum:

Pinal Dave dedi ki...

nice work!

montoya dedi ki...

Thanks a lot Pinal, especially it is your nice work.