关于“SQL Server中hierarchyid类型如何实现查询优化”的知识有一些人不是很理解，对此小编给大家总结了相关内容，具有一定的参考借鉴价值，而且易于学习与理解，希望能对大家有所帮助，有这个方面学习需要的朋友就继续往下看吧。

在数据查询中，从2008开始SQL Server提供了一个新的数据类型hierarchyid，专门用来操作层次型数据结构。

hierarchyid  类型对层次结构树中有关单个节点的信息进行逻辑编码的方法是：对从树的根目录到该节点的路径进行编码。 

这种路径在逻辑上表示为一个在根之后被访问的所有子级的节点标签序列。 表示形式以一条斜杠开头，只访问根的路径由单条斜杠表示。 对于根以下的各级，各标签编码为由点分隔的整数序列。 子级之间的比较就是按字典顺序比较由点分隔的整数序列。 每个级别后面紧跟着一个斜杠。 因此斜杠将父级与其子级分隔开。 例如，以下是长度分别为 1 级、2 级、2 级、3 级和 3 级的有效 hierarchyid 路径：

• / 

• /1/ 

• /0.3.-7/ 

• /1/3/ 

• /0.1/0.2/

在没有hierarchyid的日子里，我们通过CTE的方式来查询父以及全部的下级，但是，数据量多的情况下，CTE的方式将会变的很慢，后来，我们通过构造PATH的方式来加快速度。那么，有了hierarchyid类型后，自然得使用hierarchyid了。

现在，通过一个实际的例子来看看hierarchyid的威力。

 

一：CTE方式

WITH CTEGetChild AS  
(  
    SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4' --and [State]=0 and AuditState=2
    UNION ALL  
     (
        SELECT A.* FROM EL_Organization.Organization AS A
        INNER JOIN CTEGetChild AS B ON a.PARENTID=B.ID  --and A.[State]=0 and A.AuditState=2
     )  
)

查询出来4489行，需要25S。

看来CTE方式已经到了不能容忍的地步，那么，现在，我们就用它来进行优化。

 

二：hierarchyid

首先，我们得新建该字段，然后为其赋值，

create function f_cidname(@id varchar(50)) returns varchar(max) as 
begin 
declare @pids nvarchar(max); 
declare @pNames nvarchar(max); 
set @pids=''; 
set @pNames=''; 
with cte as 
( select id,parentid,name from EL_Organization.Organization where id =@id--'00037fdf184e48d084b87c3499e3c0e5'
union all 
select b.id,b.parentid,b.name from cte A ,EL_Organization.Organization B where a.parentid = b.id 
)

select @pids=convert(varchar(32),Convert(int, Convert(varbinary(max), id)))  + '/'+ @pids from cte 
return '/'+@pids

end 
go

接着，我们需要Update全表：

UPDATE EL_Organization.Organization SET PIDS=dbo.f_cidname(id)

注意，id是guid的32位字符串，而hierarchyid字段不支持那么大的Path内路径，于是我们将GUID转为了整型：convert(varchar(32),Convert(int, Convert(varbinary(max), id))) 

2.1 TIP

Exception message: DataReader.GetFieldType(4) returned null. Exception data: System.Collections.ListDictionaryInternal

注意，极有可能我们把字段更新上去后，我们的程序却出错了，如上。这个时候，我们需要把

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll

这个DLL打包到我们的应用程序中去。原因不解释了。

看看效果吧，修改过后的代码为：

DECLARE @tmpIds hierarchyid
SELECT @tmpIds=Pids FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4';
WITH CTEGetChild AS  (
    SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4'
    UNION ALL(
    SELECT * FROM EL_Organization.Organization WHERE Pids.IsDescendantOf(@tmpIds)=1 
    )
)
SELECT * FROM CTEGetChild

现在，我们的时间到了1S内。

2.2 一切为了不动应用层代码

现在，既然，增加了一个字段，我们就要维护这个字段，如：本条记录在应用程序中被移动到了别的父级下，就需要更新这个字段。为了不动上层代码，唯一能做的就是创建触发器，即：原有的ParentId变动的时候，就需要更新这个PIds字段，于是，我们创建触发器如下：

create trigger UpdateOrgPIds
on EL_Organization.Organization
after update
as
if update ([ParentId])
begin
     declare @tmpId varchar(36)
     select @tmpId=id from inserted 
     update EL_Organization.Organization set pids=dbo.f_cidname(@tmpId)
end 
go
-- drop  trigger EL_Organization.UpdateOrgPIds

上述内容具有一定的借鉴价值，感兴趣的朋友可以参考，希望能对大家有帮助，想要了解更多"SQL Server中hierarchyid类型如何实现查询优化"的内容，大家可以关注群英网络的其它相关文章。

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：mmqy2019@163.com进行举报，并提供相关证据，查实之后，将立刻删除涉嫌侵权内容。

猜你喜欢

• SQL通配符有哪些？一文搞懂通配符的用法

  一般我们在搜索数据库中的数据时会使用到SQL通配符，因为通配符可以替代字符串中的一个或者多个字符。这篇文章就主要介绍SQL通配符的用法，本文有详细的示例，对学习SQL通配符有一定的参考价值。

• SQL Server中查询执行计划是怎样的，怎么理解

  本文通过介绍默认使用索引、强制使用聚集索引、强制使用非聚集索引让我们知道对于检索所有列结果集使用主键的聚集索引是最佳选择。有兴趣的朋友可以看下

• 深入了解SQL Server聚合函数算法优化

  这篇文章主要介绍SQL Server聚合函数算法优化的小技巧，对大家学习SQL Server有一定的帮助，感兴趣的朋友可以参考下，希望大家阅读完这篇文章能有所收获，接下来小编带着大家一起了解看看。

• sql server 2012 FileTable有哪些功能？

  sql server 2012 filetable有哪些功能？FileTable是基于FILESTREAM的一个特性，本文我们就来简单的了解一下FileTable，它的功能有以下这些：

• SQL FIRST()函数的功能是什么，使用有什么要注意的

  SQL FIRST() 函数返回指定的列中第一个记录的值。_下文的讲解详细，步骤过程清晰，对大家进一步学习和理解相关知识有一定的帮助。有这方面学习需要的朋友就继续往下看吧！