Export (0) Print
Expand All

GetLevel (Database Engine)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns an integer that represents the depth of the node this in the tree.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

-- Transact-SQL syntax
node.GetLevel ( ) 
-- CLR syntax
SqlInt16 GetLevel ( ) 

SQL Server return type: smallint

CLR return type: SqlInt16

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).

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()
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft