The type represents a position in a hierarchical structure, specifying depth and breadth.
Assembly: Microsoft.SqlServer.Types (in Microsoft.SqlServer.Types.dll)
Thetype exposes the following members.
|CompareTo(Object)||Returns a value indicating the results of a comparison between a and an object.|
|CompareTo(SqlHierarchyId)||Returns a value indicating the results of a comparison between two nodes.|
|Equals||Evaluates whether and obj are equal. (Overrides ValueType.Equals(Object).)|
|Finalize||(Inherited from Object.)|
|GetAncestor||Retrieves the node n levels up the hierarchical tree.|
|GetDescendant||Gets the value of a descendant node that is greater than child1 and less than child2.|
|GetHashCode||Gets a hash of the path from the root node of the hierarchy tree to the node. (Overrides ValueType.GetHashCode().)|
|GetLevel||Gets a value indicating the level of the node in the hierarchical tree.|
|GetReparentedValue||Gets a value representing the location of a new node that has a path from newRoot equal to the path from oldRoot to this, effectively moving this to the new location.|
|GetRoot||Gets a value representing the root node of the hierarchy.|
|GetType||(Inherited from Object.)|
|IsDescendantOf||Gets a value indicating whether the node is the descendant of the parent.|
|MemberwiseClone||(Inherited from Object.)|
|Parse||Converts the canonical string representation of a node to a value.|
|Read||Reads from a specified binary reader into a .|
|ToString||Returns the canonical string representation of a node from a value. (Overrides ValueType.ToString().)|
|Write||Writes a to a specified binary writer.|
|Equality||Evaluates whether two nodes are equal.|
|GreaterThan||Evaluates whether one specified node is greater than another.|
|GreaterThanOrEqual||Evaluates whether one specified node is greater than or equal to another.|
|Inequality||Evaluates whether two nodes are unequal.|
|LessThan||Evaluates whether one specified node is less than another.|
|LessThanOrEqual||Evaluates whether one specified node is less than or equal to another.|
This type provides the following benefits for storing hierarchical information:
Very compact data storage.
Depth-first comparison. Indexes on this type are in depth-first order, and nodes close to each other in a depth-first traversal are stored near each other.
Support for arbitrary insertions and deletions.
A limitation of this type is that a single instance of the hierarchy data type can be no larger than 892 bytes. Hierarchies that possess too many levels to fit within this limitation must use a different data type.
The HierarchyId type is available to CLR clients as the SqlHierarchyId data type.
DECLARE @employee hierarchyid SELECT @employee = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = 'adventure-works\dylan0' SELECT * FROM HumanResources.EmployeeDemo WHERE @employee.IsDescendantOf(OrgNode) = 1