Clustered and non clustered index

Just to explain the main difference between Clustered and non clustered index with a simple example.

Clustered index makes data storage in a sorted order based on the key/s used for indexing, while non clustered index does not.

Just have a look to the below piece of T-SQL statements to remember this -


Clustered Index Example -


drop table Clustered_Table
Go

create table Clustered_Table
(
id int,

name varchar(100)
)
Go


--Create Clsutered Index
Create Clustered Index Clust_ind_id
on Clustered_Table(id)
GO


-- Insert few records
Insert into Clustered_Table Values (1, 'Avaneesh')
GO
Insert into Clustered_Table Values (5, 'Suresh')
GO
Insert into Clustered_Table Values (3, 'Dinesh')
GO
Insert into Clustered_Table Values (2, 'Mahesh')
GO
Insert into Clustered_Table Values (4, 'Vikash')
GO


O/P –


Id name
1 Avaneesh
2 Mahesh
3 Dinesh
4 Vikash
5 Suresh



Note the output is in sorted order.





Non Clustered Index Example -

drop table Non_Clustered_Table
Go
create table Non_Clustered_Table
(
id int,

name varchar(100)
)
Go


-- Create non clustered index
Create nonClustered Index test_Clust_ind1
on Non_Clustered_Table (id)
GO


-- Insert the records
Insert into Non_Clustered_Table Values (1, 'Avaneesh')
GO
Insert into Non_Clustered_Table Values (5, 'Suresh')
GO
Insert into Non_Clustered_Table Values (3, 'Dinesh')
GO
Insert into Non_Clustered_Table Values (2, 'Mahesh')
GO
Insert into Non_Clustered_Table Values (4, 'Vikash')
GO


-- get the records with non clustred index used
select * from Non_Clustered_Table



O/P –

Id Name
1 Avaneesh
5 Suresh
3 Dinesh
2 Mahesh
4 Vikash


Note the order of the records. Same as we had entered.

Hope it will help you somewhere.
Bye.