Post reply

Is searching on a non-clustered index slower when a clustered index exists?

  • March 3, 2004 at 11:32 am

    #84226

    The way I understand it is if a clustered index exists and I do a search on the nonclustered

    it will first find the root of the nonclustered index, travel through the leafs to find the correct value

    that then is used scan the clustered index vs just grabbing a pointer from the leaf and going directly to

    the data if a clustered index does not exist.

     

    Would my assumption be true?

     

    Thanks,

    Allan..

     

  • March 3, 2004 at 12:28 pm

    #497032

    You are correct.

    Clustered indexes are indexes with root, non-leafed and leafed blocks.  The difference between non-clustered indexes (on table with clustered index) and clustered indexes is that clustered index leafs blocks contain the actual table row data and non-clustered indexes (on table with clustered index) contain the clustered index.

    That means that a non-clustered index read (on table with clustered index) searches the index tree to find a match and returns all the clustered index values associated with the non-clustered index.  It will then search the clustered index for each of the found clustered index values.

    There is an algorithm (I've forgotten it's name and functionality) that says if you are doing a lookup on a non-clustered index on a table with a unique clustered index and the non-clustered index value(s) return more than 7-9% of the rows in the table, it is more efficient to do a table scan... just for the reasons of having to transverse all the non-clustered and clustered index blocks.

    Now, if you don't have a clustered index then you have a heap-table.  In this instance a pointer to the physical location on disk of the data is stored with the data.  Any non-clustered indexes then contain a reference to the same physical location on disk.

    I'm not sure but I would think that this would actually be faster than using a clustered index (for data retrieval only).  This methodology provides absolutely no data or relational integrity.  The only place I've seen common (sort of) use of heap tables is in large data warehouses and even then I personally don't like them.

    I hope this helps.

  • March 3, 2004 at 12:45 pm

    #497039

    Thanks for your help Srankin!

     

  • March 3, 2004 at 4:14 pm

    #497123

    Unless the nonclustered index is a covering index and SQL does not have to fetch back to the clustered table. This is why composite non-clustered indexes are so attractive.

    ------------

    Ray Higdon MCSE, MCDBA, CCNA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply