HierarchyId是如何存储在SQL Server中的

Question

我有一个值为'/1/'的hierarchyid。当我将它插入SQL数据库时，它存储为0x58。

这是我的桌子：

CREATE TABLE [dbo].[Category](
[CategoryId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NULL,
[HierarchyDescription] [hierarchyid] NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
[CategoryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

我是这样做的：

INSERT INTO [dbo].[Category] ([Name] ,[HierarchyDescription])
VALUES ('CAT1', '/1/');

值0x58是如何获得的？

我在C#中尝试过这样做

Encoding.ASCII.GetBytes("/1/");

我得到了十六进制的值0x2F312F(47 49 47(。

Answer 1

HierarchyId被记录为极其紧凑的

表示具有n个节点的树取决于平均扇出(节点的子节点(。对于小型扇形分叉(0-7(，大小约为6*logAn位，其中A是平均扇出。中的节点10万人的组织层级，平均粉丝数为6电平大约需要38位。这被四舍五入到40位或5字节，用于存储。

因此，很明显，它可以在一个字节中容纳多个级别。

内部格式没有记录在案，但之前有人对此进行过调查(由于我查看原始页面时没有加载原始页面，所以返回机器链接(。

免责声明：来源本身是非权威的，只是从值的检查中推断出来的，我可能在下面介绍了更多的错误

下面给出了一些相当简单的例子

WITH HierarchyIdStrings(path) As
(
SELECT '/'  UNION ALL
SELECT '/0/'  UNION ALL
SELECT '/1/'  UNION ALL
SELECT '/1/1/'  UNION ALL
SELECT '/1.1/'  UNION ALL
SELECT '/2/'    UNION ALL
SELECT '/98/'  
)
SELECT HierarchyId::Parse(path) AS HierarchyId, path, 
bin,
trimmed_bin = LEFT(bin, 1 + LEN(bin) - CHARINDEX('1', REVERSE(bin)))
FROM HierarchyIdStrings
CROSS APPLY(SELECT CONVERT(VARCHAR(1784), CAST(HierarchyId::Parse(path) AS VARBINARY(892)), 2)) V1(hex)
CROSS APPLY (SELECT 
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
hex,
'0', '0000'),'1', '0001'),'2', '0010'),'3', '0011'),
'4', '0100'),'5', '0101'),'6', '0110'),'7', '0111'),
'8', '1000'),'9', '1001'),'A', '1010'),'B', '1011'),
'C', '1100'),'D', '1101'),'E', '1110'),'F', '1111') ) V2(bin)
ORDER BY HierarchyId

结果

+-------------+-------+--------------------------+--------------------+
| HierarchyId | path  |           bin            |    trimmed_bin     |
+-------------+-------+--------------------------+--------------------+
| 0x          | /     |                          |                    |
| 0x48        | /0/   |                 01001000 |              01001 |
| 0x58        | /1/   |                 01011000 |              01011 |
| 0x5AC0      | /1/1/ |         0101101011000000 |         0101101011 |
| 0x62C0      | /1.1/ |         0110001011000000 |         0110001011 |
| 0x68        | /2/   |                 01101000 |              01101 |
| 0xE02540    | /98/  | 111000000010010101000000 | 111000000010010101 |
+-------------+-------+--------------------------+--------------------+

0x58的二进制表示是01011000，但后面的零应该被忽略，所以我们只需要关心5个比特。01011。

链接文章假设初始前缀01是为组件/0/保留的。。。CCD_ 6，所以它知道它只需要为该分量再读取三个比特。接下来的两个比特用于值本身，最后一个比特是1，因为这不是虚线分量。

/1/1/的表示只是将其中两个连接在一起。

1.1的表示将1添加到01011以得到01100，然后将01011连接到其末尾。

CCD_ 14更为复杂。这给出了111000000010010101的比特串。

链接文章指示范围/80/到/1103/将具有前缀1110，并且形式为1110aaa0zzz0y1xxx1

• aaa=000(十进制0(
• zzz=001(十进制1(
• y=0(十进制0(
• xxx=010(十进制2(

1x + 8y + 16z + 80=2 + 0 + 16 + 80=98

组件也有可能具有负数。如果对此感兴趣，请参阅链接文章！

Answer 2

我通过以下操作解决了这个问题，可能有更好的方法

hierarchyValue = "/1/";
MemoryStream memStream = new MemoryStream();
BinaryWriter binWriter = new BinaryWriter(memStream);
hierarchyValue.Write(binWriter);
byte[] propertyValue = memStream.ToArray();

存储在数据库中的HierarchyId值为propertyValue