Wednesday, 6 March 2013

SQL Server : Index Part 2 : Structure of Heap Tables

In the earlier post we tried to understand the difference between a table having clustered index and does not have a clustered index. A table with clustered index is called clustered table.A table with out clustered index is called a heap table.

Heap Table 
  • A table which does not have a clustered index.
  • Heap table have one row in sys.partitions with index_id =0
  • Data is not stored in any particular order. Not in the order of insert also.
  • As the data is not stored in any specific order, data can not be retrieved quickly.
  • Data pages are not linked to each other.
  • To read the data from the data pages, it has to refer back the IAM (Index Allocation Map) pages.
  • The first_iam_page column, in the sys.system_internals_allocation_units system view, points to the first IAM page in the chain of IAM pages that manage the space allocated to the heap.
  • As there is no clustered index, fragmentation can not be addressed by rebuilding the index.
  • SQL server used the IAM pages to navigate through the heap structure. The data pages allocated to the heap are not in any specific order and are not linked. The only logical connection between the the data pages is the information stored in the IAM pages.
Each IAM pages store the allocation (single page and extent allocation) done for a single object. A table scan of a heap table can be performed by scanning the IAM pages to find the single pages and extents that are holding data pages of the heap.

To find out the IAM page, use the below command

DBCC IND('databasename','Tablename',-1)

In the output of the above query, record with value 10 for Page Type column are the IAM pages 

A typical heap structure is given below

If you liked this post, do like my page on FaceBook at


  1. Hi admin, i went through article. As we all know, .net is most popular programming language and it offer huge career prospects for talented professionals. It’s totally awesome, keep on updating your blog with such awesome information.
    DOT NET Training in Chennai


  2. I loved the way you discuss the topic great work thanks for the share, Let me share this, Hadoop training in pune

  3. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Cloud Computing Project Center in Chennai | Cloud Computing Projects in Velachery

  4. It is amazing and wonderful to visit your site..Thanks for sharing this information,this is useful to me...Data Mining Projects Center in Chennai | Data Mining Projects Center in Velachery.

  5. There was very wonderful information and that's great one. I really appreciate the kind words, thanks for sharing that valuable information.
    Power System Project Center in Chennai | Power System Project Center in Velachery

  6. I have to voice my passion for your kindness giving support to those
    people that should have guidance on this important matter.

    java training in chennai