Diff cluster vs noncluster Index in sql

Introduction

In this article i am going to say about the way of creating index which are cluster and nonclusterd index,along with some description about both.

First create two table named
* clusterindex * nonclusterindex

code:

create table nonclusterindex(Emid int,data varchar(800))
create table clusterindex(Emid int,data varchar(800))

insert data to nonclusterindex in same order you can jest copy and past it

code:

insert into nonclusterindex values (4,replicate ('b',100))
insert into nonclusterindex values (1,replicate ('z',100))
insert into nonclusterindex values (2,replicate ('a',100))
insert into nonclusterindex values (0,replicate ('y',100))

In the same way insert data into clusterindex table

code:

insert into clusterindex values (4,replicate ('b',100))
insert into clusterindex values (1,replicate ('z',100))
insert into clusterindex values (2,replicate ('a',100))
insert into clusterindex values (0,replicate ('y',100))

create noncluster index for the table nonclusterindex

code:

create unique nonclustered index nonclusterindex_emid on nonclusterindex (Emid)

create cluster index for the table clusterindex

code:

create unique clustered index clusterindex_emid on clusterindex (Emid)

after that select clusterindex data

code:select * from clusterindex----------------the out put will be of sorted one

output:

0 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy
1 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
4 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

you me see that out put is been sorted by Emid.

select nonclusterindex data from that table

code:
select * from nonclusterindex-------------the output will be nonsorted one

output:

4 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
1 zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
2 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
0 yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

this is the mager difference between two indexing
as clustered index output is sorted one,we can index only one row,
but we can index more than one row in nonclusterd indexing

we can also use primary key for unique key

by default index will be of nonclustered index type
like this eg..
--create unique index nonclusterindex_emid on nonclusterindex (Emid)

Know insert more data to it

code:

insert into nonclusterindex values (-1,replicate ('zz',100))

the output will be in same order as was inserted

In the same way insert data into clusterindex table

code:

insert into clusterindex values (-1,replicate ('zz',100))

the output will be as sorted one

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here