GetLevel (Database Engine)
Returns an integer that represents the depth of the node this in the tree.
Syntax
-- Transact-SQL syntax
node.GetLevel ( )
-- CLR syntax
SqlInt16 GetLevel ( )
Return Types
SQL Server return type: smallint
CLR return type: SqlInt16
Remarks
Used to determine the level of one or more nodes or to filter the nodes to members of a specified level. The root of the hierarchy is level 0.
GetLevel is very useful for breadth-first search indexes. For more information, see Hierarchical Data (SQL Server).
Examples
A. Returning the hierarchy level as a column
The following example returns a text representation of the hierarchyid, and then the hierarchy level as the EmpLevel column for all rows in the table:
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeDemo
B. Returning all members of a hierarchy level
The following example returns all rows in the table at the hierarchy level 2:
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeDemo
WHERE OrgNode.GetLevel() = 2
C. Returning the root of the hierarchy
The following example returns the root of the hierarchy level:
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeDemo
WHERE OrgNode.GetLevel() = 0
D. CLR example
The following code snippet calls the GetLevel() method:
this.GetLevel()