Home Forums Article Discussions Article Discussions by Author Discuss content posted by Gail Shaw Introduction to Indexes: Part 3 – The nonclustered index RE: Introduction to Indexes: Part 3 – The nonclustered index

  • July 6, 2012 at 8:10 am

    #1509708

    deroby (7/6/2012)

    Hi Gail, another thanks for the series!

    We've always used the 'rule' that every table needs a clustered index and I've never really challenged that. If the PK is lean enough, we use that, otherwise we put an IDENTITY(int) column to the table and take that... a simple no-brainer in most cases.

    However... suppose we have a large table (wide & long) that gets INSERTs and UPDATEs on non-indexed fields only.

    Would it make sense to 'force' it as a heap and use non-clustered indexes only because I (naively?) would expect that the Bookmark lookup (**) would be A LOT faster using the RID than using the clustered-index key(s); the latter requiring zigzagging through the entire (clustered) b-tree again ... ??

    Well, if your UPDATEs sometimes make, say, VAR* columns larger, then you're likely to see table fragmentation, which is best fixed by normal index maintenance on the clustered index.

    I would say that the ability to do index maintenance is another very solid argument for "everything gets a clustered index".