SQL Server JOIN on partition_id or hobt_id?

tkclm6bt  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(113)

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

7gs2gvoe

7gs2gvoe1#

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.

相关问题