Computed Hierarchyid Column

Question

I have a table that uses a ParentID column; the standard way of representing a hierarchy.

I have read about all the goodies of hierarchyid and need it to query all records that have a specific ancestor. The problem is that converting the table to use hierarchyid would require many changes that I currently do not have time for.

Is it possible to create:

a computed column that is of type hierarchyid

OR

an indexed view of that table with a hierarchyid

I thought of making a function that computes the hierarchyid recursively but that would not be good in terms of performance.

Answer 1

Have a look at using a reqursive CTE