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: Database
your post is informative. That's what all about clustered vs non-clustered.