层次结构 ID：使用 SQL Server 2008 构建数据层次结构模型

核心提示： 本文基于 SQL Server 2008 RC0 的预发布版本，文中的所有信息均有可能发生变更，层次结构 ID：使用 SQL Server 2008 构建数据层次结构模型，本文将介绍以下内容： 分层数据建模 创建材料清单系统 转向 HierarchyID 测试系统 本文使用了以下技术：SQL Server 2

本文基于 SQL Server 2008 RC0 的预发布版本。文中的所有信息均有可能发生变更。

本文将介绍以下内容：

分层数据建模

创建材料清单系统

转向 HierarchyID

测试系统

本文使用了以下技术：

SQL Server 2008

目录

分层数据

材料清单问题

使用实体理解问题

SQL Server 2005 中的 BOM 系统

创建表

通过查询验证设计

利用 HierarchyID

测试 HierarchyID 实现

结束语

汽车行业所使用的制造系统；分为州、郡、市的国家组织结构和邮政编码；家庭娱乐系统的说明——它们之间有什么共同点？答案很简单，它们都描述了一种层次结构。

SQL Server 2008 支持新的数据类型——HierarchyID，它有助于解决建模和查询分层信息中的一些问题。我将以制造中经常使用的材料清单 (BOM) 或清单为例，向您介绍这种数据类型。我先简要讨论一下 BOM，然后讲解如何为这种类型的数据建模。我还会展示这种模型在 SQL Server 2005 中的实现。然后为您介绍如何使用 HierarchyID 数据类型在 SQL Server 2008 中实现这种模型。

分层数据

汽车由许多种零部件组合而成，例如发动机、传动系统、电子设备和转向装置等。在美国，地理版图首先划分为州，然后再划分成称为郡的行政单位。然后再由不同的机构以不同的方式进一步对郡进行划分。例如，美国人口局由人口普查区组成。美国邮政服务通过 ZIP 代码邮递邮件。地理信息系统 (GIS) 可以将人口普查区和 ZIP 代码组合在一起，以便为用户提供更为人们所熟知的地域空间参照。

最近专程来到本地一家电子用品商店评估一套置换家庭娱乐系统，觉得它也类似分层系统——所有可能的组件和选件的组合令人眼花缭乱！我真想知道该系统如何在数据库系统中建模和实现。

汽车与其发动机之间的关系也代表一种层次关系：汽车包含发动机。与传动系统、电子设备和转向装置的关系与此相同。这种关系称为包容。从地理或人口普查数据不同分组之间的关系中也可以观察到类似的层次结构。

层次结构无处不在，但是要在关系数据库的上下文中实现这种层次结构却非易事。典型的方法是使用有一个或多个表的父/子关系表示层次结构。尽管此方法在很多情况下确实可以发挥功效，但它存在几个缺点。例如，解决方案必须认真考虑如何保持引用完整性。尽管在 SQL Server 2005 中通过引入递归的通用表表达式大大简化了查询表的深度和广度，但需要连接许多表时，编写对这些类型的表的查询仍然是个问题。

材料清单问题

几年前，我使用过由某个制造公司开发的系统，该系统可以帮助其经销商指定构建中心枢轴灌溉系统所需的组件。该软件生成自主构建所需枢轴（中心枢轴灌溉系统主体在行业内简称枢轴）的组件清单。根据地理位置、土壤类型和该区域计划种植的农作物，以及设备自身的水文和结构考虑确定必要的组件。

解决方案以 SQL Server 数据库为基础。数据库用来存储可用于构建枢轴的组件的相关信息。但是，当生成制造规格时，我们需要将这些组件标识为 BOM。

一些清单代表将要装配成系统组件的实际零部件的集合。例如，每个枢轴均需要使用泵将水从井中抽入系统。泵可能会采用电源供电，这意味着它还需要变压器和保险丝盒。它也可能采用燃料提供动力，这意味着它需要油箱、燃油泵和连接油箱的软管。无论何种情况，泵所需的零部件都将列在泵清单中。

完整的枢轴清单还包括其他一些清单。例如，标准枢轴包含泵清单、运送水的管线清单以及构建该枢轴系统所需的任何其他设备的清单。

使用实体理解问题

第一个要问的问题是：在表示层次结构的系统中，需要考虑哪些实体以及这些实体有哪些属性？不要深究试图建模的底层系统中单个元素的语义，这是个窍门。

例如，以图 1 所示的基于 PC 的家庭影院系统为例。很明显，此处所示的每个项都代表一个实体。这虽然没错，但它却难以代表公司可能销售的所有家庭影院系统。如果某些系统使用数字视频录像机 (DVR) 代替 PC 将如何？对于不想要无线调谐器的消费者又将如何？

图 1 家庭影院系统中的组件

此类变化恰好解释了为什么 BOM 系统适合解决分层数据建模问题。它使得完整的系统能够分解成许多不同的子清单和二级子清单，直到最终分解为树形数据结构中的单个零部件。位于树根部的是由单个清单代表的完整系统。第一级分支通常也由清单组成。子清单列表继续向下分层，直到清单仅包含实际系统零部件的列表，并且这些零部件将成为树的叶节点。

那么系统中包含哪些实体呢？其中有只包含零部件（零部件清单）的清单，以及包含其他清单的清单。

零部件有说明和成本。（当然，它还可以有许多其他属性，但为了便于说明，我们仅使用这两种属性。）零部件清单的属性可能包含所需的零部件及其数量。从零部件自身信息中分离出所需特定零部件的数量非常重要。如果无法做到这一点，那您可能会得到许多不同数量的零部件重复实例。规范化规则认为在大多数情况下这种设计不完善。

清单会包含说明，通过这种方式它可以与其父清单及相关零部件清单相关联。图 2 代表一组实体及其关系。

图 2 实体关系

尽管关系数据库在表示大多数关系时非常出色，但它并不十分适合用于多对多的实体模型。原因并不是物理表不能处理数据；而是难于创建表之间的引用完整性限制。这种关系还会导致复杂的查询。但当您第一次处理表的逻辑设计时，该问题比较容易修复。您只需在零部件清单和清单表之间插入一个表即可。该表将只存储对清单的引用和对零部件清单的引用。图 3 显示包含此附加表的实体关系图。

图 3 简化材料清单系统的实体关系图

SQL Server 2005 中的 BOM 系统

既然已经了解了 BOM 系统的理论基础，那么现在即可动手实现它。过程相当简单。

创建数据库

创建实体关系图中所示的表

为相应的表添加所需的约束

根据需要为表配置访问权限

用测试数据填充表

针对表编写并执行查询以便验证设计

以下是创建示例数据库的 T-SQL 代码。您可能需要更改文件名路径以便在自己的系统上使用：

create database [dbBom] on　primary (　
　name = n'dbBom', filename = n'c:msdnmagdbBom.mdf',
　size = 50mb , maxsize = unlimited, filegrowth = 2mb )
log on (
　name = n'dbBom_log', filename = n'c:msdnmagdbBom_log.ldf',
　size = 10mb , maxsize = 2048gb , filegrowth = 10%);

成功执行完该语句后，请确保将数据库范围更改为 dbBOM，例如使用 USE dbBOM 语句。

创建表

以下是创建四个必要表的可执行 T-SQL 代码：

create table dbo.part(
　partID smallint not null,
　descr varchar(50) not null,
　cost money not null);
create table dbo.partList(
　partListID int not null,
　partID smallint not null,
　quantity smallint not null);
create table dbo.billPartList(
　billID int not null,
　partListID int not null);　
create table dbo.bill(
　billID int not null,
　parentBillID int null,　
　descr varchar(50) not null);

请注意，这些表中唯一允许有 null 值的列是 dbo.bill 中的 parentBillID 列。该 null 值使我们能够将代表完整系统的清单与其他清单区别开来。

使此实现引用有效的关键是保证表约束正确无误。表在解决方案中具备不同的功能，因此具有不同的约束。但是，SQL Server 中的每个表都应该具有与其相关联的 primary key（主键），如下所示：

alter table dbo.part　
　add constraint pkPart
　primary key clustered(partID);
alter table dbo.bill
　add constraint pkBill
　primary key clustered(billID);
alter table dbo.partList　
　add constraint pkPartList
　primary key clustered(partListID);
alter table dbo.billPartList
　add constraint pkBillPartList
　primary key clustered(billID,partListID);

其中某些列应始终具有唯一的值，像零部件或清单的说明 (descr)。您也可以通过要求 partID 和所需零部件数量的组合来防止用户创建重复的零部件清单条目：

alter table dbo.part　
　add constraint uqDescr
　unique(descr);
alter table dbo.bill
　add constraint uqBill
　unique(descr);
alter table dbo.partList　
　add constraint uqPartList
　unique(partID,quantity);

最后，可以在表之间添加外键约束。第一个约束存在于 dbo.part 和 dbo.partList 之间。这要求添加到零部件清单中的零部件必须首先在 dbo.part 表中定义。如果从 dbo.part 中删除了零部件，您希望能在零部件清单中反映出这一变化。可以通过为约束添加 "on delete cascade" 子句轻松地完成这项任务。由于 partID 标记为标识符，所以它无法更改，并因此而无法进行级联操作：

alter table dbo.partList　
　add constraint fkPartList_Part
　foreign key(partID)
　references dbo.part(partID)
　on delete cascade
　on update no action;

下一个约束要求如果为清单分配 parentBillID，则 billID 引用的清单必须存在于 dbo.bill 表中。级联约束则有所不同。您无法在这里设置级联约束，因为 SQL Server 无法保证当删除或更新父清单时可能会发生无穷递归操作。由于没有定义默认值，所以 "set default" 也不是一个选项。

alter table dbo.bill　
　add constraint fkBill_Bill
　foreign key(parentBillID)
　references dbo.bill(BillID)
　on delete no action
　on update no action;

billPartList 表需要一对外键约束。首先，您需要要求将与清单建立联系的零部件清单存在于 PartList 表中。此外，引用的清单必须位于 dbo.bill 表中。由于 dbo.bill 中的 billID 和 dbo.partList 中的 partListID 都不会被更新，因此不可能对此约束进行级联操作。但是，如果删除零部件或清单，则这些更改可能会影响此表：

alter table dbo.billPartList
　add constraint fkBillPartList_PartList
　foreign key(partListID)
　references dbo.partList(partListID)
　on delete cascade
　on update no action;
alter table dbo.billPartList
　add constraint fkBillPartList_Bill
　foreign key(billID)
　references dbo.bill(billID)
　on delete cascade
　on update no action;

您现在可以将一些数据插入到该表中进行测试。考虑到所涉及的数据量，我没有在此处显示这些代码。如果下载本文的示例代码，只需打开并运行名为 01_data.sql 的脚本即可生成此示例数据。

通过查询验证设计

编写并执行一些简单的查询将有助于测试和验证该设计是否能够按照预期方式工作。我的第一个查询使用新的通用表表达式语法生成分层清单列表：

with c as (
　select billID,parentBillID,descr,0 as [level]
　from dbo.bill b
　where b.parentBillID is null
　　union all
　select b.billID,b.parentBillID,
　　b.descr,[level] + 1
　from dbo.bill b join c on b.parentBillID =
　　c.billID)
select descr,[level],billID,parentBillID
　as bill
from c

如图 4 中返回的数据所示，只有一个单独的根节点（其中 parentBillID 为 null）。如果有多个这样的清单，您可能需要更改查询，以便按 ID 选择该父清单。（有关通用表表达式的更多信息，请参阅 2007 年 10 月的“数据点”专栏，网址为 msdn.microsoft.com/magazine/cc163346。）

图 4 查询清单层次结构

下一个查询构建于第一个查询之上，它可以列出系统清单（由第一个查询返回）中所使用的每个零部件。该查询利用表之间的所有关系：

with c as (
　select billID,parentBillID,descr,0 as [level]
　from dbo.bill b
　where b.parentBillID is null
　　union all
　select b.billID,b.parentBillID,b.descr,[level] + 1
　from dbo.bill b join c on b.parentBillID = c.billID)
select p.partID,p.descr
from c
join dbo.billPartList bpl on c.billID = bpl.billID
left join dbo.partList pl on bpl.partListID = pl.partListID
join dbo.part p on pl.partID = p.partID
group by p.partID,p.descr;

下一个查询通过求和所需系统零部件的成本并乘以 2 计算制造商建议的系统零售价格 (MSRP)：

with c as (
　select billID,parentBillID,descr,0 as lvl
　from dbo.bill b
　where b.parentBillID is null
　　union all
　select b.billID,b.parentBillID,b.descr,lvl + 1
　from dbo.bill b join c on b.parentBillID = c.billID)
select SUM(p.cost*pl.quantity) * 2.0
　from c
　join dbo.billPartList bpl on c.billID =　　 bpl.billID
　left join dbo.partList pl on　　 bpl.partListID= pl.partListID
　join dbo.part p on pl.partID = p.partID

下一个查询反转之前的查询。它没有采用从最顶层父清单开始向下遍历数据的方式，它是从一个零部件开始，查询其所属的清单，然后按层次结构向上依次找出所有使用该零部件的清单：

with c as (
　select b.descr,b.billID,b.parentBillID,0　　 as lvl
　from dbo.partList pl
　left join dbo.billPartList bpl on　　pl.partListID = bpl.partListID
　left join dbo.bill b on bpl.billID =　　 b.billID
　where pl.partID = 19
　　union all
　select b.descr,b.billID,b.parentBillID,lvl+1
　from dbo.bill b
　join c on c.parentBillID = b.billID)
select * from c;

使用行式列表查看清单之间的关系比较困难。在这种情况下，最好将清单组织成路径，如下所示。这样就可以轻松地看到清单之间的嵌套关系：

with c as (
　select '/'+cast(billID as varchar(49)) as path,BillID
　from dbo.bill b
　where b.parentBillID is null
　　union all
　select cast(c.path+'/'+CAST(b.billID as varchar(4)) as varchar(50)),　　b.billID
　from dbo.bill b join c on b.parentBillID = c.billID)
select c.path+'/',b.descr
from c join dbo.bill b on c.billID = b.billID
order by 1;

您可以看到图 5 中的结果嵌套。

图 5 嵌套 BOM 列表

剩余的查询演示向系统添加新的清单——在本例中，假设公司将把 4GB 内存条作为“夏季奖励促销”的一部分。图 6 中的代码在层次结构中插入新的零部件和表示它的清单。

图 6 添加新的零部件和清单

begin transaction
　declare @mp int;
　declare @mpl int;
　declare @mb int;
　select @mp = MAX(partID)+10 from dbo.part;
　insert into dbo.part(partID,descr,cost)
　　output inserted.*
　　values (@mp,'4GB USB 2.0 Memory Stick',20.0);
　select @mpl = MAX(partListID)+10 from dbo.partList;　
　insert into dbo.partList(partListID,partID,quantity)
　　output inserted.*
　　values (@mpl,@mp,1);
　select @mb = MAX(billID)+10 from dbo.bill;
　insert into dbo.bill(billID,parentBillID,descr)
　　output inserted.*
　　values (@mb,110,'Summer Bonus Package')
　insert into dbo.billPartList(billID,partListID)
　　output inserted.*
　　values (@mb,@mpl);
commit;
go
select * from dbo.bill where parentBillID = 110;
select * from dbo.part;
go

现在假设有人指出“夏季奖励促销”清单不应该直接位于总系统清单之下，而应该位于 Disk Drive module 模块之下。在关系数据库中这很容易更改：

update dbo.bill set parentBillID = 320 where billID = 507
go
select * from dbo.bill where parentBillID = 320;
go

在夏季促销期结束时，删除这个新清单也很简单：

begin transaction
　delete from dbo.billPartList where billID=507;
　delete from dbo.bill where billID=507;
　delete from dbo.part where partID=45;
commit;
go
select * from dbo.billPartList;
select * from dbo.bill;

利用 HierarchyID

HierarchyID 数据类型是基于 CLR 的二进制表示，用于存储顺序路径的紧凑二进制表示。由于它是内置数据类型，所以并不需要专门激活 SQL/CLR 功能即可使用。当需要表示各值之间的嵌套关系，并且该关系可以采用顺序路径语法表达时，HierarchyID 非常有用。

顺序路径看起来有点像文件路径，但它不使用目录和文件名，而是使用数值。与其他父/子关系类似，所有顺序路径都必须归结于根节点。在 SQL Server 2008 中，根节点的文本表示是一个 (/) 字符。具有顺序路径的元素通常由整数表示，但也可能使用小数。顺序路径必须使用另一个 (/) 字符结束。

但顺序路径并不以文本形式存储在数据库中。而是将其采用数学方法散列成二进制值，然后将这些二进制值存储在数据页中。

那我们需要如何修改现有的数据库才能使用 HierarchyID 呢？图 7 显示了一些示例清单所用的顺序路径。因为示例数据中的父/子关系位于清单表中，所以该表是逻辑起点。我不用修改此表，取而代之，我将添加名为 bill2 的新表。

图 7 示例材料清单中使用的路径

我将添加名为 billPath 的列，其类型为 HierarchyID。我将在此列中存储清单之间的父/子关系（使用顺序路径格式）。我可在此删除某列以导航至父行，这种能力对很多查询都很有帮助。我没有选择将父 ID 作为静态数据字段存储，而是使用根据该导航类型的层次结构 ID 计算出的保留列。更新后的表创建代码如下：

create table dbo.bill2(
　billPath HierarchyID not null,
　billID smallint not null,
　parentBillPath as billPath.GetAncestor(1) persisted,
　descr varchar(50) not null);

如果以前没有用过基于 Microsoft .NET Framework 的数据类型，parentBillPath 定义中的方法调用可能不太容易理解。调用按类型定义的方法，并有选择性地向其传递一个或多个参数的能力可以大大简化这段代码。

其中您可以看到 HierarchyID 类型的 GetAncestor 方法。HierarchyID 以紧凑的二进制格式存储路径。您既不能简单地引用此二进制值的某些区段并将其用作 billID，也不能直接提取某个清单顺序路径的某些子区段。为此，您可以利用 GetAncestor 方法。这里的参数值表示“返回以此节点父项结尾的顺序路径”。该参数值控制顺序路径的截取长度。因此这里您将得到当前清单父项的顺序路径。图 8 总结了 HierarchyID 数据类型的可用方法，以及每种方法的一些典型用法。

图 8 HierarchyID 方法

方法	返回结果	参数值	用途
GetAncestor	代表父级或更高级元素顺序路径的 HierarchyID。	整数值，指明沿当前顺序路径向上遍历的级数。	找出顺序路径中此实例的父级、祖父级或更高级别的元素。
GetDescendent	HierarchyID，代表当前节点的子节点、孙子节点或更低级别子孙节点的路径。	两个 HierarchyID 实例，其中一个可以为 null 或者两者都为 null，用于限制可能返回的子项。	获取路径，以便在当前顺序路径中某个深度处插入新元素。
GetLevel	16 位整数值，代表顺序路径的总深度。	无。	确定两条顺序路径是否具有相同的深度。
GetRoot	具有零个元素的顺序路径的 HierarchyID。	无。	找出任意顺序路径的绝对根。
IsDescendantOf	如果作为参数传入的顺序路径是调用实例的子项，则返回 1。	HierarchyID 实例。	确定给定的 HierarchyID 是否是另一个实例的子项。
Parse	HierarchyID 实例。	顺序路径的文本表示。	从给定的路径创建 HierarchyID 实例。当 HierarchyID 实例设置为字符串时将隐式调用此方法。
GetReparentedValue	在将当前项目从一条路径移动到另一条路径时，代表完整顺序路径的 HierarchyID。	当前顺序路径表示为 HierarchyID，且目标顺序路径也表示为 HierarchyID。	将一行或多行值从一条父顺序路径移动到另一条父顺序路径。
ToString	HierarchyID 顺序路径的文本表示。	无。	分析 HierarchyID 的顺序路径。

接下来，您需要创建新表的主键及其外键约束：

alter table dbo.bill2
　add constraint pkBill2
　primary key(billPath);
alter table dbo.bill2
　add constraint fkBill2Parent
　foreign key (parentBillPath) references dbo.bill2(billPath);

在本例中，我使用 HierarchyID 值作为主键和外键。请记住，HierarchyID 代表深度优先的顺序路径。因此，同级路径之间的值差异很大，而父级与子级路径之间的差异则较小。当使用 HierarchyID 值作为表的群集索引时，沿相同路径的节点比不同路径上的节点存储位置更接近。在很多情况下，这种设计很好，因为常常需要查询给定路径的所有子节点。但是，如果经常查询特定节点，您可能会希望对派生自 GetLevel 的列创建群集索引。

这种特殊的实现会带来一个敏感的问题。因为是对路径而不是值建立外键约束，因此我需要使用表中的现有值作为基点。所有清单链都需要一个基点，幸运的是，它们都使用相同的基点。HierarchyID GetRoot 方法实际上是解决此问题的最佳方法。考虑以下语句：

insert into dbo.bill2(billPath,billID,descr)
　values (hierarchyID::GetRoot(),0,'All Bills');

此处，我通过调用 GetRoot 方法作为该类型的静态成员，创建代表任何层次结构中最可能根节点的清单。现在当我插入第一级节点时，它们仍将引用当前表中的父节点。

那如何才能将现有清单从 dbo.bill 迁移到 dbo.bill2？您真正需要做的只是修改前面所示选择层次结构路径的查询，以便它能够将路径表示为 HierarchyID，然后将数据插入 dbo.bill2。以下是 T-SQL 代码：

with c(billID,[path],[descr]) as (
　select b.billID,'/'+CAST(b.billID as varchar(max)),descr
　from dbo.bill b
　where parentBillID is null
　　union all
　select b.billID,c.path+'/'+CAST(b.billID as varchar(max)),b.descr
　from dbo.bill b
　join c on b.parentBillID = c.billID)
insert into dbo.bill2(billpath,billID,descr)
select path+'/' as [path],billID,descr
from c
order by c.path;

图 9 显示 dbo.Bill2 的内容。

图 9 dbo.bill2 的前 15 行

现在您可能还希望更新与 dbo.BillPartList 的外键关系，如下所示：

alter table dbo.billPartList
drop constraint fkBillPartList_Bill
go
alter table dbo.billPartList
　add constraint fkBillPartList_Bill2
　foreign key(billPath)
　references dbo.bill2(billPath)
　on delete cascade
　on update no action;

测试 HierarchyID 实现

在已创建并填充表且设置好约束的基础上，我可以重写前面的测试查询以使用新的 HierarchyID 类型。第一个查询通过使用递归的通用表表达式自顶 (bill 110) 向下生成分层清单列表。当我使用 HierarchyID 数据类型时，任何给定清单的完整家系均已由该值代表。例如，层次结构最底层的清单之一 bill 405 代表 CPU 套件。那么，我如何才能知道其家系由哪些清单组成呢？以下代码可以告诉我结果：

select billPath.ToString() from dbo.bill2 where　 billID=405;

此查询的输出是值为 "/110/210/310/405" 的字符串。现在的问题变成如何才能确切得到该字符串所代表的清单？这个问题有些困难，因为 HierarchyID 类型并不提供将这种值列表转换成矢量的方法。但是您可以通过使用 T-SQL 或 SQL/CLR 编写自己的方法。以下是使用 T-SQL 表值函数编写的代码：

create function dbo.Vectorize(@i hierarchyID)
returns @t table (position int identity(1,1)
　not null,nodeValue int not null)
as begin
　declare @list varchar(max) = @i.ToString();
　declare @delimit int;
　set @list = substring(@list,2,len(@list)-1);
　while len(@list) > 1 begin
　　set @delimit = charindex('/',@list);
　　insert into @t values (cast(substring(@list,1,@delimit-1) as int));
　　set @list = substring(@list,@delimit+1,len(@list));
　end;
　return;
end;

现在您可以通过一个非递归查询非常方便地检查系统中任意清单的家系：

declare @anyBill int = 405;
select b1.billPath,b2.billID,b2.descr from dbo.bill2 b1
　cross apply dbo.vectorize(b1.billPath) p　
　join dbo.bill2 b2 on b2.billID = p.nodeValue
　where b1.billID = @anyBill;

要想找出给定清单所使用的零部件又该如何呢？与前面介绍过的需要递归查询清单列表的实现不同，下面这个查询可以利用 HierarchyID 的功能，通过使用 IsDescendantOf 方法确定某条顺序路径是否是另一条路径的后代：

declare @anyBill int = 110;
declare @sourceBillPath hierarchyID;
select @sourceBillPath = billPath from dbo.bill2 where billID = @anyBill;
select p.partID as 'partID',
　p.descr 'partName'
　from dbo.bill2 b2　　
　left join dbo.billPartList bpl on b2.billID = bpl.billID
　left join dbo.partList pl on bpl.partListID = pl.partListID
　left join dbo.part p on pl.partID =p.partID
　where b2.billPath.IsDescendantOf(@sourceBillPath)=1
　and p.partID is not null
　order by p.partid;

通过对前面介绍的技术进行一些修改即可计算整个系统或任何子清单的 MSRP：

declare @anyBill int = 110;
declare @sourceBillPath hierarchyID;
select @sourceBillPath = billPath
from dbo.bill2 where billID = @anyBill;
　select SUM(p.cost * pl.quantity)*2.0
　from dbo.bill2 b2　　
　left join dbo.billPartList bpl on b2.billID = bpl.billID
　left join dbo.partList pl on bpl.partListID = pl.partListID
　left join dbo.part p on pl.partID =p.partID
　where b2.billPath.IsDescendantOf(@sourceBillPath)=1;

可以使用 GetAncestor 方法查询确定哪个清单使用了特定的零部件。该方法允许您沿层次结构向上查询。这里我需要使用递归向上遍历整个清单列表：

declare @partID int = 20;
with c(billPath,descr) as (
　select b.billPath,b.descr
　from dbo.part p
　join dbo.partList pl on p.partID = pl.partID
　join dbo.billPartList bpl on pl.partListID = bpl.partListID
　join dbo.bill2 b on bpl.billID = b.billID
　where p.partID = @partID
　　union all
　select b.billPath,b.descr
　from dbo.bill2 b
　join c on b.billPath = c.billPath.GetAncestor(1))
select distinct descr,billPath
from c
where billPath <> hierarchyID::GetRoot()
order by billPath;

图 6 添加了新的零部件和清单。不需要对代码进行任何更改即可插入零部件或创建零部件清单。但创建清单确实为使用 HierarchyID 的 GetDescendant 和 GetReparentedValue 方法提供了机会。我可以使用以下代码段启动该过程：

begin　
　begin transaction
　declare @mp int;
　declare @mpl int;
　select @mp = MAX(partID)+10 from dbo.part;
　insert into dbo.part(partID,descr,cost)
　　output inserted.*
　　values (@mp,'4GB USB 2.0 Memory Stick',20.0);
　select @mpl = MAX(partListID)+10 from dbo.partList;　
　insert into dbo.partList(partListID,partID,quantity)
　　output inserted.*
　　values (@mpl,@mp,1);

创建完零部件和零部件清单后，我现在可以创建相关的清单。我的想法是将新清单插入到组成 Super X100 Home Theatre System 的所有其他清单的右侧。要完成这项工作，我需要知道两件事：父清单的路径及其最右侧子项的路径。

获取父清单路径的查询很简单。而要找出其最右侧子项则需要使用 IsDescendantOf 和 GetLevel 方法，如下所示：

declare @root hierarchyID;
declare @newBillPath hierarchyID;
declare @newBillID int;
select @root = billPath
　from dbo.bill2
　where billID = 110;
select @newBillPath = max(billPath)
　from dbo.bill2
　where billPath.IsDescendantOf(@root) =1
　and billPath.GetLevel() = @root.GetLevel()+1;

我查找根节点的所有直系后代。由于路径按值排序，所以具有 HierarchyID 最大值的清单将成为最右侧的实例。知道最右侧的对等节点后，我现在需要紧邻它创建新的路径。这正好是 GetDescendant 的用武之地。传递给该方法的参数控制在何处创建新路径。此处列表中第二个参数为 null，这表示“在第一个参数的右侧创建新节点”：

select @newBillPath =@root.GetDescendant(@newBillPath,null);　

现在我已经获得了新清单的路径，并且该路径具有 newBillID 的值，因此我只需要提取该值。前面添加的表值函数正好可以派上用场：

select @newBillID = nodeValue
　from dbo.Vectorize(@newBillPath)
　where position = @newBillPath.GetLevel();

最后，我可以通过将新清单插入 dbo.bill2 表，并将 billID 和 partListID 插入 dbo.billPartList 表完成此工作：

insert into dbo.bill2
　output inserted.*
　values (@newBillPath,@newBillID,'Summer Bonus Package');
insert into dbo.billPartList(billID,partListID)
　output inserted.*
　values (@newBillID,@mpl);
commit;
end;

只要不更改 billID，要将 Summer Bonus 清单从 Super X100 Home Theatre System 的直接子项变为 Disk Drive Module 的子项并不难（参见图 10）。切记调用 GetReparentedValue 本身并不会影响表中保存值的任何更改。您需要使用更新反映这些更改。

图 10 移动清单

begin
begin transaction
declare @oldPath hierarchyID,@newRoot hierarchyID;
declare @newPath hierarchyID;
declare @billIDToMove int = 251;
declare @billIDToMoveTo int = 320;
select @oldPath = billPath
　from dbo.bill2 where BillID = @billIDToMove;
select @newRoot = b.billPath
　from dbo.bill2 b where billID = @billIDToMoveTo
select @newPath = @oldPath.GetReparentedValue(
　@oldPath.GetAncestor(1),@newRoot);
update dbo.bill2
　set billPath = @newPath
　output inserted.*
　where billID = 251;
commit;
end;

删除零部件及其 matchingID 清单可能非常简单，也可能需要花一番功夫。这主要取决于删除的清单是否是另一个清单的父项。如果不是，例如 Summer Bonus 清单，您可以直接删除该清单及其零部件：

begin
declare @partToDelete int = 45;
begin transaction
delete from dbo.bill2 where billID in
　(　select billID from dbo.billPartList bpl
　join dbo.partList pl on bpl.partListID = pl.partListID
　join dbo.part p on pl.partID = p.partID
　where p.partID = @partToDelete);
delete from dbo.part where partID = @partToDelete;　　
commit;
end;

删除一个或多个清单的父项（类似于本例中的设计）需要更为复杂的代码。问题在于存在要求每个清单都具有直接父项的外键约束。如果试图删除父清单，则会引发错误。要解决此问题，我必须首先将所有子清单均移动到仍将存在的清单之下。为此，我需要暂时丢弃外键约束，并使用 GetReparentedValue 方法为新节点计算路径。

结束语

SQL Server 2008 中新的 HierarchyID 类型使紧凑型数据能够存储并使用可以标识节点的顺序路径。几乎所有依靠分层数据模型的系统都可以使用这种新的数据类型实现。

它的主要优势在于可以减少实际存储在磁盘上的数据量，并且可以编写较为简单的查询。请注意，使用这种类型的同时仍然可以利用本机 SQL Server 对级联引用完整性约束的支持。与使用子 ID 和父 ID 标量值的传统层次结构建模方法不同，通过使用 HierarchyID，每个节点都将包含其完整的路径作为标识，这一点请务必牢记。