What are best practices to setup Indexes on Heap table on production SQL Server?


  • There are Fragmentations on some of tables on my DB. only two tables are having indexes and others are HEAP

    I want to setup indexes on those HEAP tables and remove fragmentation .

    Can anyone advise, what are best practices and prerequisite to setup indexes on PROD Server.


    Wednesday, June 27, 2018 9:52 AM

All replies

  • Creating a non-clustered index doesn't affect the data pages for a heap table. I.e. it will not remove "fragmentation" for the heap.

    Exactly what do you mean by fragmented heaps? In what way are they fragmented. Heaps aren't sorted, so there's nothing that can become fragmented in the first place.

    Unless you mean forwarded records, of course. In that case, you can do ALTER TABLE ... REBUILD. See

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, June 27, 2018 10:57 AM
  • Well almost everybody says that every table must have clustered index, I do not want to get into discussion but  yes, you can choose the column/s that will be a clustered index , moreover, should it be unique or none unique.......

    Just some very general tips

    a) Choose smallest datatype (INT...) for CI

    b) Avoid having GUIDs for  CI

    c) Avoid having column that frequently updated  for CI 

    First create CI and then NCI  in the table 


    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, June 27, 2018 11:34 AM
  • Let me clear there is no concept of fragmentation in heap, heap is collection of unordered and scattered pages in SQL Server. You are correct when you say you want to create Index, I would add go ahead and create clustered index, if you create NCI it will still remain heap. Lot of people who use SQL Server 2008 or above rebuild heap to remove fragmentation but creating CI is what is recommended to remove fragmentation.

    Look at answer given in below thread

    How to lower heap fragmentation in SQL Server

    Paul Randal: Fixing Heap Fragmentation



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles


    Thursday, June 28, 2018 7:37 AM