• h.tobisch (11/21/2014)


    Just understood:

    a nonclustered index may be the same size but never larger than a clustered index, so, if it covers the data the nonclustered index will be first choice.

    I don't believe that that is correct. The non-clustered index can be larger (require more pages of storage) than the clustered index.

    In a case like the index here, the leaf level of the non-clustered index is the same size as the leaf level of the non-clustered index. If there are enough rows the non-leaf levels of the non-clustered index will be bigger than the non-leaf levels of the clustered index simply because the cluster key is smaller than the other index's key, so that the total size of the non-clustered index will be greater than the total size of the clustered index.

    Tom