Sql-server – the meaning of hbcolid field

sql serversystem-tables

I just connect to SQL Server and invoke the following command:

select * from sys.sysrscols

The result table contain a field hbcolid, which seem to be identical to rscolid. I think rscolid is surely for rowset column id. Then what is the meaning and usage of hbcolid?

Is there any document on this?

Best Answer

Given that this is an internal system table that you can only access when connecting via the DAC, no, there is no documentation on this. What are you using this table for?

I found several instances here where rscolid was not identical to hbcolid. For example:

SELECT rsid, rscolid, hbcolid
  FROM sys.sysrscols
  -- WHERE rscolid <> hbcolid
  WHERE rsid = 72057594038714368;

rsid                 rscolid   hbcolid
-----------------    -------   -------
72057594038714368    4         2

Where does this row come from?

SELECT OBJECT_NAME(object_id)
  FROM sys.partitions 
  WHERE partition_id = 72057594038714368;

-------------------------
queue_messages_1003150619

So what indexes might be involved?

SELECT index_id, name
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('sys.queue_messages_1003150619');

index_id   name
--------   ---------------------
1          queue_clustered_index
2          queue_secondary_index

So going on a guess, I ran this query:

SELECT 
  ic1.index_id,
  c.name,
  ic1.column_id, -- column id in the table
  ic1.key_ordinal -- column order in the index
FROM sys.all_columns AS c
INNER JOIN sys.index_columns AS ic1
ON c.[object_id] = ic1.object_id
AND c.column_id = ic1.column_id
AND ic1.column_id <> ic1.key_ordinal
WHERE c.object_id = OBJECT_ID('sys.queue_messages_1003150619');

One of the rows here:

index_id   name                   column_id   key_ordinal
--------   ---------------------  ---------   -----------
1          conversation_group_id  4           2

So, without doing much more investigation, my initial guess is that rscolid is the column id within the base table, and hbcolid is the ordinal position of that column in the index. This holds true for 5 of the columns in the clustered index of this queue table - they all have identical mismatches in sys.sysrscols, and also for one of the columns in the non-clustered index.

But again, whether I'm right or wrong, I'm not sure what you could possibly do with that information: what are you using this table for?

Related Question