Export (0) Print
Expand All

SqlHierarchyId Structure

The SqlHierarchyId type represents a position in a hierarchical structure, specifying depth and breadth.

Namespace:  Microsoft.SqlServer.Types
Assembly:  Microsoft.SqlServer.Types (in Microsoft.SqlServer.Types.dll)

[SqlUserDefinedTypeAttribute(Format.UserDefined, IsByteOrdered = true, 
	MaxByteSize = , Name = "SqlHierarchyId")]
[CLSCompliantAttribute(true)]
public struct SqlHierarchyId : IBinarySerialize, 
	INullable, IComparable

The SqlHierarchyId type exposes the following members.

  NameDescription
Public propertyIsNullGets a value indicating whether the SqlHierarchyId is null.
Public propertyStatic memberNullGets a SqlHierarchyId with a hierarchy identification of null.
Top

  NameDescription
Public methodCompareTo(Object)Returns a value indicating the results of a comparison between a SqlHierarchyId and an object.
Public methodCompareTo(SqlHierarchyId)Returns a value indicating the results of a comparison between two SqlHierarchyId nodes.
Public methodEqualsEvaluates whether SqlHierarchyId and obj are equal. (Overrides ValueTypeEquals(Object).)
Public methodGetAncestorRetrieves the SqlHierarchyId node n levels up the hierarchical tree.
Public methodGetDescendantGets the value of a descendant SqlHierarchyId node that is greater than child1 and less than child2.
Public methodGetHashCodeGets a hash of the path from the root node of the hierarchy tree to the SqlHierarchyId node. (Overrides ValueTypeGetHashCode.)
Public methodGetLevelGets a value indicating the level of the SqlHierarchyId node in the hierarchical tree.
Public methodGetReparentedValueGets a value representing the location of a new SqlHierarchyId node that has a path from newRoot equal to the path from oldRoot to this, effectively moving this to the new location.
Public methodStatic memberGetRootGets a value representing the root SqlHierarchyId node of the hierarchy.
Public methodGetType (Inherited from Object.)
Public methodIsDescendantOfGets a value indicating whether the SqlHierarchyId node is the descendant of the parent.
Public methodStatic memberParseConverts the canonical string representation of a SqlHierarchyId node to a SqlHierarchyId value.
Public methodReadReads from a specified binary reader into a SqlHierarchyId.
Public methodToStringReturns the canonical string representation of a SqlHierarchyId node from a SqlHierarchyId value. (Overrides ValueTypeToString.)
Public methodWriteWrites a SqlHierarchyId to a specified binary writer.
Top

  NameDescription
Public operatorStatic memberEqualityEvaluates whether two SqlHierarchyId nodes are equal.
Public operatorStatic memberGreaterThanEvaluates whether one specified SqlHierarchyId node is greater than another.
Public operatorStatic memberGreaterThanOrEqualEvaluates whether one specified SqlHierarchyId node is greater than or equal to another.
Public operatorStatic memberInequalityEvaluates whether two SqlHierarchyId nodes are unequal.
Public operatorStatic memberLessThanEvaluates whether one specified SqlHierarchyId node is less than another.
Public operatorStatic memberLessThanOrEqualEvaluates whether one specified SqlHierarchyId node is less than or equal to another.
Top

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

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Community Additions

ADD
Show:
© 2014 Microsoft