Search code examples
sql-serverdmv

JOIN on partition_id or hobt_id?

I'm joining the SQL Server DMVs sys.column_store_segments and sys.partitions. Logically, I would join on partition_id:

SELECT *
FROM sys.column_store_segments AS s
INNER JOIN sys.partitions AS p
 ON p.partition_id = s.partition_id

But I have also seen examples that join on hobt_id, for instance in the canonical documentation:

SELECT *
FROM sys.column_store_segments AS s
INNER JOIN sys.partitions AS p
 ON p.hobt_id = s.hobt_id

Does it matter in practice? I guess it is guaranteed to be the same unique heap-or-b-tree anyway?

Interestingly enough Microsoft writes:

You can uniquely identify a segment using <hobt_id, partition_id, column_id>, <segment_id>.

But IIRC, from hobt_id and partition_id only one would be necessary here...?

Solution

  • Ah. Not only are they as unique, they are the exact same thing!

    They're the same thing. They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    So in fact they are interchangeable.