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 Boyd
~~~
This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.
About Chad Boyd
Chad is an Architect, Administrator, and Developer with technologies such as Sql Server (and all related technologies), Windows Server, and Windows Clustering. He currently works as an independent consultant and also spends a significant amount of time writing, talking, presenting and blogging about Sql Server in person and online at http://mssqltips.com. In the past, Chad has worked with companies and organizations such as Microsoft Corporation and The American Red Cross, and provided consulting/support services at companies such as Bank of America, HP, Citigroup, Qualcomm, Scottrade, TJX, SunTrust, and Zurich Financial Services. For over 3 years with Microsoft Corporation Chad was responsible for providing onsite and remote support, guidance, and advice with SQL Server products to some of Microsoft’s foremost enterprise customers running the largest, most complex SQL Server installations and configurations in the world. This included all SQL Server products and versions, including SQL Server 7.0, 2000, 2005, and recently 2008, the SQL Server database engine, Reporting Services, SSIS/DTS, Notification Services, and Analysis Services on both 32 and 64 bit systems. Chad's primary responsibilities today include troubleshooting critical server situations, performance tuning and monitoring, disaster recovery planning and execution, architectural guidance for new Sql Server related deployments, and delivering deep technical workshops/presentations/proof-of-concept sessions covering a variety of technologies and functionality. Chad regularly posts Sql Server related content, tools, and advice with the mssqltips team at http://blogs.mssqltips.com/blogs and http://mssqltips.com. Chad can be contacted via his blog or email at chad dot boyd dot tips at gmail dot com.
