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
create table tbl_tags
TagId int not null ,
Name varchar(255)

Insert some rows

declare @i int = 1000

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

Retrieve Row by filtering Name column

select * from tbl_tags where Name =  '1' 

Execution Plan is the picture below


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


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)
select * from tbl_tags where Name = '1'

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.