这个问题困扰了我一段时间,希望 SQL Server 专家能够解答。问题是:当你对包含 UDT(CLR 类型)的 SQL Server 列建立索引时,SQL Server 如何确定要执行哪个索引操作以适应给定的查询?具体来说,我想到的是 hierarchyid(也称为 SqlHierarchyID)类型。Microsoft 建议的使用方式 - 以及我使用的方式 - 是:
1. 在 hierarchyid 列本身上创建一个索引(我们称之为 ID)。这样可以进行深度优先搜索,因此当您编写 WHERE ID.IsDescendantOf(@ParentID) = 1 时,它可以执行索引查找。
2. 创建一个持久化计算的 Level 列并在 (Level, ID) 上创建一个索引。这将启用广度优先搜索,因此当您编写 WHERE ID.GetAncestor(1) = @ParentID 时,它可以执行索引查找 (在第二个索引上)。
但是我不明白的是,这是如何实现的?它似乎违反了正常的查询计划规则 - 对 GetAncestor 和 IsDescendantOf 的调用似乎并不可搜索,因此这应该导致完全的索引扫描,但事实并非如此。显然我不会抱怨,但我正在尝试理解是否可以在自己的 UDT 上复制此功能。hierarchyid 是否只是 SQL Server 具有特殊意识的“神奇”类型,并且如果发现某个查询元素和索引的特定组合,它会自动更改执行计划?还是 SqlHierarchyID CLR 类型只是定义了一些特殊属性/方法(类似于持久化计算列的 IsDeterministic 如何工作)被 SQL Server 引擎理解?
我似乎找不到任何关于这个问题的信息。我能找到的所有信息都是一个段落,说明 IsByteOrdered 属性通过保证每个实例具有一个唯一表示,使索引和检查约束等功能成为可能;虽然这有点有趣,但它并不能解释 SQL Server 如何能够使用某些实例方法执行查找。
所以问题又来了 - 对于 hierarchyid 等类型,索引操作是如何工作的?是否可以在新的 UDT 中获得相同的行为?
1. 在 hierarchyid 列本身上创建一个索引(我们称之为 ID)。这样可以进行深度优先搜索,因此当您编写 WHERE ID.IsDescendantOf(@ParentID) = 1 时,它可以执行索引查找。
2. 创建一个持久化计算的 Level 列并在 (Level, ID) 上创建一个索引。这将启用广度优先搜索,因此当您编写 WHERE ID.GetAncestor(1) = @ParentID 时,它可以执行索引查找 (在第二个索引上)。
但是我不明白的是,这是如何实现的?它似乎违反了正常的查询计划规则 - 对 GetAncestor 和 IsDescendantOf 的调用似乎并不可搜索,因此这应该导致完全的索引扫描,但事实并非如此。显然我不会抱怨,但我正在尝试理解是否可以在自己的 UDT 上复制此功能。hierarchyid 是否只是 SQL Server 具有特殊意识的“神奇”类型,并且如果发现某个查询元素和索引的特定组合,它会自动更改执行计划?还是 SqlHierarchyID CLR 类型只是定义了一些特殊属性/方法(类似于持久化计算列的 IsDeterministic 如何工作)被 SQL Server 引擎理解?
我似乎找不到任何关于这个问题的信息。我能找到的所有信息都是一个段落,说明 IsByteOrdered 属性通过保证每个实例具有一个唯一表示,使索引和检查约束等功能成为可能;虽然这有点有趣,但它并不能解释 SQL Server 如何能够使用某些实例方法执行查找。
所以问题又来了 - 对于 hierarchyid 等类型,索引操作是如何工作的?是否可以在新的 UDT 中获得相同的行为?