I'm new to HierarchyId data type SQL Server 2008 and not sure why I have to pass a value as '/0/2/' to get descendent of a node in text. This is OK when there are limited number of records or having a very small table but when it gets to thousands or millions of records how should I use rather '/0/2/' ?
-- Get all the nodes under a specific node
select *, emp_id.GetLevel() as level, emp_id.ToString() as StringFormat
where emp_id.IsDescendantOf('/0/2/') = 1
order by emp_id;
Any help is appreciated.