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

29 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. 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
  3. 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
  4. 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
  5. 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
  6. Awesome Blog.. I impressed while I read such a great blog. Thanks for sharing.. CCNP Training Institute in Chennai

    ReplyDelete
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information, this is useful to me…
    Software Testing Summer Courses in Adyar | Summer Courses in Velachery | Java Summer Courses in Perungudi

    ReplyDelete
  14. I like the valuable information you provide in your articles. I will bookmark your weblog and check again here frequently.
    Graphics Designing Training Institute in chennai | Best Multimedia courses in Velachery

    ReplyDelete
  15. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas. Linux Exam Center in Chennai | CCNA Exam Center in Chennai | CCNP Exam Center in Chennai | Tally ERP9 Exam Center in Chennai

    ReplyDelete
  16. I like your blog format as you create user engagement in the complete article. It seems round up of all published posts. Thanks for sharing, such a nice article. UIPath Exam Center in Chennai | Automation Anywhere Exam Center in Chennai | Blue Prism Exam Center in Chennai

    ReplyDelete
  17. Hi. Thank you for Your valuable information. Keep posting useful information.

    Java Course in Chennai
    Java Training Institute in Chennai

    ReplyDelete
  18. Thanks for this grateful information. all this information is very important to all the users and can be used good at all this process.


    Best Web Designing Training Academy in Kanchipuram

    ReplyDelete
  19. Thank you so much for this useful article. Visit OGEN Infosystem for Web Designing and SEO Services in Delhi, India.
    SEO Service in Delhi

    ReplyDelete
  20. Machine learning (ML) algorithms allows computers to define and apply rules which were not described explicitly by the developer. machine learning and ai courses in hyderabad

    ReplyDelete
  21. This post is so helpfull and attractive.keep updating with more information...
    Future Of Data Science
    Data Science Subjects

    ReplyDelete