This is the web Diary and I will blog whenever I feel good to blog. I don't know how blogs were classified .. this is just a Personal Diary or my own views upon things happening around me. Most of the Posts in this blog are written in Myanmar (Burma) Native Language, known as Burmese/Myanmarsar (Not Myanmese or Barmen). This blog is composed with Winkalaw Font. I recommend you to get the Winkalaw 3 (modified) from the link below.



Download WinKalaw Font Unofficial Version 3 for Better Reading

Clustered Index Vs Non-clustered Index ?
 
What are they ???
 
The major differences is you can have Only One Clustered index for each table.
But you can have multiple Non-Clustered Index for that table.
 
Normally Clustered index built upon the Primary Key. Because Clustered Index order the rows according to the order of physical data of that table.
 
Non-Clustered index doesn't need to based on physical order, they order the index by logical order. Leaf node of non-clustered index doesn't contain datapage, leaf nodes contain index rows.
 
When they should be used ?
 
The ultimate answer is "It depends".
 
Let's say, you use the primary key on RowID with clustered index. If you want to access/query the table based on RowID .. that will be better with this clustered index.
 
Bottom line for Clustered Index is .. apply clustered index for virtually uniqued, mostly ordered and mostly queried column. Because you can't have another clustered index for that table. So choose wisely.
 
( If you are using clustered index over not-so-unique or even multiple-identical rows ... you may lost the advantage of clustered index )
 
But sometime .. you may need to work with other non-sorted columns such as Name, Description ( especially , varchar/char typed columns ). Then your physical data store cannot be ordered physically. In this case ... you should use Non-Clustered Index for these columns. Because Non-Clustered Indexes can have logical sorting order.
 
Why they need to be used ?
 
The simple answer is to speed up your operation especially when you are having huge database and when you need to perform a lot of vast rows manipulation.
 
Indexes are not the only solution. You have to use them with other optimization machanisms like Partition, Views, Optimize the queries .. etc etc.

Labels:

Published by Kalvin on Tuesday, October 02, 2007 at 2:11 PM.

1 Responses to “Clustered Index Vs Non-clustered Index”

  1. # Blogger narender

    your post is informative. That's what all about clustered vs non-clustered.  

Post a Comment

Links to this post

Create a Link


Presented by Kalvin