Pratical Advantage of Heap

  • Hi all experts,

    Is there any practical advantage of using Heap as a storage instead of clustered index for table.

  • Not many. It may give slightly faster inserts but there's enough downsides that it;s not usually a good idea. Unless you've tested and know that a heap is faster for the operations you're doing on a table.

    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
  • Bulk insert into a staging table and then building the clustered and other indexes afterwards will generally be faster.

    This is also useful when you then want to switch the data into an existing partitioned table.

  • I've seen clustered tables with a key that was so poorly implemented, I wish the developer had just left it as a heap. A heap will perform better and require less maintenance (less fragmentation) than a table with a non-sequential, changing, or extremely wide clustered key. That means don't cluster on something like a GUID, zip code, or customer phone number, because SQL Server will expend a lot of I/O splitting pages and organizing rows.

    However, a table with a well chosen clustered key is generally best. Most large transactional tables have the equivalent of a transaction_date, insert_date, sequence_number, or some sequentially incrementing column with static values that would make a good candidate for a clustered key.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

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

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