Difference between Cluster index and non-cluster index? [Resolved]

Posted by gurramsra1-10894 under Sql Server on 1/14/2012 | Points: 10 | Views : 3700 | Status : [Member] | Replies : 5
What is the Difference between Cluster index and non-cluster index?
Please tell me

Sra1


Responses

Posted by: Muhsinathk on: 6/21/2012 [Member] Bronze | Points: 50

Up
0
Down

Resolved
Mark as Answer if its helpful to you...

gurramsra1-10894, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 1/14/2012 [Member] Silver | Points: 25

Up
0
Down

Physical Sorting of records is Clustered index
Logical Sorting of records is Non-Clustered index

A table can have 1-Clustered index but 1-many non-clustered index.

to know more
http://www.sql-server-performance.com/2004/index-data-structures/

Thanks,
Sanjay

gurramsra1-10894, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/21/2012 [Member] Starter | Points: 25

Up
0
Down
CLUSTERED INDEX :

PHYSICAL SORTING ORDER IS THE CLUSTERED INDEX.
EACH TABLE CAN HAVE ONLY ONE CLUSTERED INDEX.
DEFAULT PRIMARY KEY IS THE CLUSTERED INDEX.

NONCLUSTERED INDEX:

LOGICAL SORTING IS THE NON CLUSTERED INDEX.
IN SQL SERVER 2005 EACH TABLE 256 NON CLUSTERED ABLE TO CREATE
DEFAULT UNIQUE KEY IS AN NON CLUSTERED INDEX

REGARDS


sriram

gurramsra1-10894, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/21/2012 [Member] Bronze | Points: 25

Up
0
Down
Clustered index
A clustered index sorts and stores the data rows of the table or view in
order based on the clustered index key. The clustered index is
implemented as a B-tree index structure that supports fast retrieval of
the rows, based on their clustered index key values.

CREATE CLUSTERED INDEX MyIndex1 ON MyTable(Column1);

Nonclustered index
A nonclustered index can be defined on a table or view with a clustered
index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

CREATE NONCLUSTERED INDEX MyIndex ON MyTable(Column1,Column2);


gurramsra1-10894, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ketvin on: 6/21/2012 [Member] Starter | Points: 25

Up
0
Down
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.

gurramsra1-10894, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response