Pages

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

23 comments:

  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

    ReplyDelete



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

    ReplyDelete
  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

    ReplyDelete
  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.

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  7. Awesome Blog.. I impressed while I read such a great blog. Thanks for sharing.. CCNP Training Institute in Chennai

    ReplyDelete
  8. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
    No.1 Software Testing Training Institute in Chennai | Best Selenium Training Institute in Chennai | Web Designing Training Institute in Chennai

    ReplyDelete
  9. Very happy to see this blog. Gives a wonderful information with coded explanation. Thank you for this blog. very useful to me.
    Best Selenium Training Institute in Chennai | Selenium Training Institute in Velachery

    ReplyDelete
  10. Thanks for sharing this unique and informative content which provided me the required information..Java Project Center in Chennai | Java Project Center in Velachery

    ReplyDelete
  11. Nice Post! It is really interesting to read from the beginning & I would like to share your blog to my circles, keep your blog as updated.Thanks
    Vacation Classes in Chennai | Best Technical Boot Camp in Chennai

    ReplyDelete
  12. Impressive blog with lovely information. Really very useful article for us thanks for sharing such a wonderful blog...
    Best Summer Courses in Chennai|Best Summer Courses in Velachery

    ReplyDelete
  13. Very nice blog. I appreciate your coding knowledge. This blog gave me a good idea to
    developed the android application.Thanks for sharing
    Summer Courses in Perungudi | Summer Courses in OMR | Summer Courses in Velachery

    ReplyDelete
  14. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    Best Photoshop Summer Courses in Guindy | No.1 Technical Boot Camp in Chennai

    ReplyDelete