SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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 http://www.facebook.com/practicalSqlDba


I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.


Leave a comment on the original post [www.practicalsqldba.com, opens in a new window]

Loading comments...