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