sql server - Efficient way to get ancestors - materialized path -

i have hierarchical data structure stored using materialized paths.

table:files  node  parentnode  name    path  100    null       f1     /f1/  101    100        f2     /f1/f2/  102    101        f3     /f1/f2/f3/ 

i have node column primary key(clustered)

now if want find ancestors of f3, given path, this:

select * files '/f1/f2/f3/' [path] + '%' 

the problem is, execution plan uses clustered index scan( think sql server defaults table scans)

is there anyway can find ancestors of node, given path in more efficient manner, preferably not using cte? have depth column @ disposal if required.

if have slow moving hierarchies, consider adding range keys. facilitate navigation, filtration, and/or aggregration without need of recursion.

the range keys indicate ownership between x , y. range keys helpful when dealing large hierarchies (180k nodes).

the following simplified example, may help.

sample hier build

--drop table #myhier  declare @yourtable table (id int,parentid  int,name varchar(50)) insert @yourtable values   (11, null,'a') ,(12, 11   ,'b') ,(13, 12   ,'f') ,(14, 13   ,'c') ,(15, 13   ,'d') ,(16, 11   ,'e') ,(17, 12   ,'g') ,(18, null ,'m') ,(19, 18   ,'n') ,(20, 18   ,'o') ,(21, 20   ,'p')  declare @top    int         = null      --<<  sets top of hier try 3  declare @nest   varchar(25) = '|-----'  --<<  optional: added readability  ;with ctep (       select seq  = cast(10000+row_number() on (order name) varchar(500))             ,id             ,parentid              ,lvl=1             ,name              ,path = cast('/'+name+'/' varchar(500))         @yourtable         isnull(@top,-1) = case when @top null isnull(parentid ,-1) else id end       union        select seq  = cast(concat(p.seq,'.',10000+row_number() on (order r.name)) varchar(500))             ,r.id             ,r.parentid              ,p.lvl+1             ,r.name              ,cast(p.path + '/'+r.name+'/' varchar(500))         @yourtable r       join   ctep p on r.parentid  = p.id)      ,cter1 (select *,r1=row_number() on (order seq) ctep)      ,cter2 (select a.id,r2=max(b.r1) cter1 join cter1 b on (b.seq a.seq+'%') group a.seq,a.id ) select a.r1         ,b.r2       ,a.id       ,a.parentid        ,a.lvl       ,name = replicate(@nest,a.lvl-1) + a.name       ,path   #myhier  cter1  join cter2 b on a.id=b.id 

select full hier

-- full hier select *   #myhier  order r1 

returns

get ancestors

-- ancestors of node declare @getancestors int = 15 select a.*    #myhier  join  (select r1 #myhier id=@getancestors) b    on  b.r1 between a.r1 , a.r2  order a.r1 

returns

select descendants

-- descendants of node declare @getdesendants int = 12 select a.*    #myhier  join  (select r1,r2 #myhier id=@getdesendants) b    on  a.r1 between b.r1 , b.r2  order a.r1 

returns