The same customer that promted the post of mine titled “How can I get sysindexes information in Sql 2005?” posed a follow-up question – what is the hobt_id column in the sys.partitions catalog view and how does it relate to other catalog views/structures?
A very good question…first, hobt (pronounced “hobbit”) stands for “Heap or B-Tree”. Sql Server uses Heap structures to store data when a given table has no cluster (i.e. a clustered index), and uses a B-Tree structure (B-Tree stands for “Balanced Tree”, not binary tree as is a common misconception) for indexes (including clustered indexes). A Heap is a collection of data pages that have no specific order and are not linked in any manner (like clusters which are a doubly-linked list).
So, a hobt in Sql Server 2005 is basically a collection of data/index pages – specifically, hobt is generally used to describe a subset of data/index pages within a single partition. Currently, the relationship between a partition and a hobt is ALWAYS 1-to-1 – meaning the hobt_id and partition_id value in the sys.partitions table are completely interchangable at the moment. In fact, you’ll notice that the actual values for partition_id and hobt_id are always exactly the same currently in Sql 2005. In some future release of Sql this may change, however there’s no talk of it at this point to my knowledge anyhow.
Chad is an Architect, Administrator, and Developer with technologies such as Sql Server (and related technologies), .NET, Windows Server, and the Microsoft Clustering stack (MSCS, NLB, HPC). After nearly 5 years at Microsoft in a variety of roles related to pushing the limits of the Microsoft stack in many of the largest, most complex Sql Server installations in the world, he now provides consulting to many different customers and clients ranging from startup to Fortune 50. Most recently he has been spending much of his time as an architect at SpruceMedia, Inc., a startup firm in the Facebook/Social Advertising space (http://sprucemedia.com) and resides in the Redmond, WA area where he also spends a significant amount of time writing, talking, presenting and blogging about the data-related technologies on the Microsoft stack. Chad regularly posts Sql Server related content, tools, and advice with the MSSQLTips team and on stackoverflow.com at http://stackoverflow.com/users/169012/chadhoc. Chad can be contacted via email at chad dot boyd dot tips at gmail dot com.