torvin
torvin

Reputation: 7091

Indexed view on hierarchical data

I have some tree-like data that is implemented using hierarchyid. Some nodes in that tree can be "locked" and when that happens, all the subnodes of the locked nodes become locked too.

Whether the node is locked or not is calculated with a relatively expensive join, but I need that data available everywhere I deal with my tree in my app. So ideally I was thinking of creating an indexed (aka materialised) view that has the list of all the locked nodes and subnodes. Unfortunately I can only come up with the query that joins the Tree table twice, which isn't allowed for indexed views. Here's my pseudocode:

with Tree as (
    -- this is my real Tree table
    select 1 Id, cast('/1/' as hierarchyid) Ord 
    union all
    select 2, '/1/1/'
    union all
    select 3, '/1/2/'
    union all
    select 4, '/1/2/1/'
    union all
    select 5, '/1/2/1/1/'
    union all
    select 6, '/2/'
    union all
    select 7, '/3/'
    union all
    select 8, '/3/5/'
), Locked as (
    -- this is the list of locked nodes (in reality obtained by joining several tables together)
    select 3 Id
    union all
    select 7
)

-- this is what I would like to capture in an indexed view
select t2.Id
from Tree t
join Locked ld on t.Id = ld.Id
join Tree t2 on t2.Ord.IsDescendantOf(t.Ord) = 1

This produces a result set that I could have left join-ed to my Tree table to check if each row is "locked" or not, but unfortunately I cannot make it into an indexed view because of the "self join" on Tree.

Is there a better way of doing something like this?

PS: Should I ask this on DBA SE instead?

Upvotes: 0

Views: 171

Answers (0)

Related Questions