• Sean Grebey (7/2/2013)


    I was asking because from what I understand if there is a clustered index non-clustered indexes use the clustered key, otherwise it uses the rowID (file number, page number and slot number), so it would seem that having a clustered index would improve performance of the non-clustered indexes but I haven't actually found anywhere that said so.

    You could argue that adding a clustered index, makes the non-clustered indexes slower. As long as the table is a heap, lookup is by RID, that is you go straight to the data. With a clustered index, there are a few more logical reads to traverse the tree of the clustered index.

    Then again, with the clustered key as part of the non-clustered index, their may be queries where the index becomes covering, which is a huge performance boost.

    As it stands, in SQL Server, clustered index is the norm, and you should only use heaps if you know exactly what you are doing. (In other products, the reverse may apply.) You may gain some performance with heaps, but you lose a lot in manageability, which in the end will affect your performance negatively.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]