Tell me difference between clustered and non clustered index in sql server

Posted by Prabu_Spark under Sql Server on 5/10/2013 | Points: 10 | Views : 18762 | Status : [Member] | Replies : 4
Hi sir,
Tell me difference between clustered and non clustered index usages in sql server with suitable examples. Kindly
give me the solution for this problem.



[Note: what is clustered and non clustered index with real time example]

With regards,
J.Prabu.
[Email:prbspark@gmail.com]



Responses

Posted by: Satyapriyanayak on: 5/10/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
A clustered index is a special type of index that reorders the way records in the table are physically
stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain
the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match
the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of
the data pages. Instead, the leaf nodes contain index rows.

Refer
http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL-2005
http://www.sql-server-performance.com/forum/threads/cluster-and-non-cluster-index.6667/

If this post helps you mark it as answer
Thanks

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/12/2013 [Member] Starter | Points: 25

Up
0
Down
There are two Types of Index available in Sql Server:

1) Clustered Index
2) Non Clustered Index

Index is used for increase the performance application.
1) Clustered Index sequential order .primary key default clustered index. each table only once clustered index.

2) unique key default non clustered index. There 256 non clustered index in each table.

Hope this will help..............

sriram

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saranya Boopathi on: 5/23/2013 [Member] Starter | Points: 25

Up
0
Down
Clustered Index:
Clustered Index can be defined as the way on which the records of the table are physically stored. In cluster index, while inserting data itself it sorted in heap order. Only one cluster index can be creating per table.
A clustered index will be created while a primary key is created on the table.
We can create an index while creating a table and also we can create index separately.
Syntax for creating Index:

create table Table_Name 

(
Column_Name datatype constraint Constraint_Name Primary Key
)


NonClustered Index:
A NonClustered index is just a pointer to the data which points to the clustered index where the data is sorted. In NonClustered index the data are store in the form of logical order. We can create NonClustered index on 249 columns in a table.
For Example Non Clustered Index is an index of the book and clustered index is an actual page of the book.

create Nonclustered index NClx_Idx_SNo on Index_Table(Sno)


Saranya Boopathi

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Annaharris on: 5/24/2013 [Member] Starter | Points: 25

Up
0
Down
Satyapriyanayak is absolutely right about Clustered and Non-clustered indexing in Sql Server.

Prabu_Spark, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response