Advantages of using nonclustered index after using clustered index on one table

Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ?

Thanks

Yes, you are correct. A nonclustered index like an index in a book "points" to a particular record which makes them ideal for exact matches queries. A NCI will either point to a rowid in a heap situation or the key in a CI-based table.

BOL 2005 excerpt:

If the underlying table is sorted using a clustered index, the location is the clustering key value; otherwise, the location is the row ID (RID) comprised of the file number, page number, and slot number of the row

HTH,

Derek

|||

see more info on the other post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=522859&SiteID=1

creation of Clustered index must happen first because

if NCIs are created first. The creation of a clustered index later will

force the recreation of all the NCIs to force it to use CI's rather than RID as locator

thereby causing a lot of trouble in the database.

|||

It is not entirely accurate that rows are sorted and stored in a clustered index. The b-tree structure ensures logical ordering of the rows and physically the rows may not be sorted in the same manner or present next to one another in a page for example. Please take a look at the link below for series of BOL topics on indexes:

http://msdn2.microsoft.com/en-us/library/ms189271.aspx