Post reply

Clustered Index or Heap?

  • September 26, 2014 at 1:21 am

    #397408

    Hi There,

    how do I know if I should create an Non clustered Index on a Clustered Index or on a heap?

    Thanks

  • September 26, 2014 at 2:40 am

    #1748397

    All tables, almost without exception, should have a well-chosen clustered index.

    http://qa.sqlservercentral.com/articles/Indexing/68563/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 26, 2014 at 3:29 am

    #1748407

    100% agreement. With few exceptions, every table should have a clustered index. Selecting that index is actually part of designing the database. The one consideration I would add on top of what Gail has written, is that you probably are best served by having the clustered key be the most common access path to the data. Frequently, even usually, this is the primary key. But it isn't always. And sometimes, it's not unique. That's OK. It is some added overhead, but not the end of the world. Since the data is stored with the clustered key at the leaf level, it just makes sense for this to be the most commonly used point of retrieval for your data.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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