Share via


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)

Syntax

'Declaration
<SqlUserDefinedTypeAttribute(Format.UserDefined, IsByteOrdered := True,  _
    MaxByteSize := , Name := "SqlHierarchyId")> _
<CLSCompliantAttribute(True)> _
Public Structure SqlHierarchyId _
    Implements IBinarySerialize, INullable, IComparable
'Usage
Dim instance As SqlHierarchyId
[SqlUserDefinedTypeAttribute(Format.UserDefined, IsByteOrdered = true, 
    MaxByteSize = , Name = "SqlHierarchyId")]
[CLSCompliantAttribute(true)]
public struct SqlHierarchyId : IBinarySerialize, 
    INullable, IComparable
[SqlUserDefinedTypeAttribute(Format::UserDefined, IsByteOrdered = true, 
    MaxByteSize = , Name = L"SqlHierarchyId")]
[CLSCompliantAttribute(true)]
public value class SqlHierarchyId : IBinarySerialize, 
    INullable, IComparable
[<SealedAttribute>]
[<SqlUserDefinedTypeAttribute(Format.UserDefined, IsByteOrdered = true, 
    MaxByteSize = , Name = "SqlHierarchyId")>]
[<CLSCompliantAttribute(true)>]
type SqlHierarchyId =  
    struct
        interface IBinarySerialize
        interface INullable
        interface IComparable
    end
JScript suports the use of structures, but not the declaration of new ones.

The SqlHierarchyId type exposes the following members.

Properties

  Name Description
Public property IsNull Gets a value indicating whether the SqlHierarchyId is nulla null reference (Nothing in Visual Basic).
Public propertyStatic member Null Gets a SqlHierarchyId with a hierarchy identification of nulla null reference (Nothing in Visual Basic).

Top

Methods

  Name Description
Public method CompareTo(Object) Returns a value indicating the results of a comparison between a SqlHierarchyId and an object.
Public method CompareTo(SqlHierarchyId) Returns a value indicating the results of a comparison between two SqlHierarchyId nodes.
Public method Equals Evaluates whether SqlHierarchyId and obj are equal. (Overrides ValueType.Equals(Object).)
Protected method Finalize (Inherited from Object.)
Public method GetAncestor Retrieves the SqlHierarchyId node n levels up the hierarchical tree.
Public method GetDescendant Gets the value of a descendant SqlHierarchyId node that is greater than child1 and less than child2.
Public method GetHashCode Gets a hash of the path from the root node of the hierarchy tree to the SqlHierarchyId node. (Overrides ValueType.GetHashCode().)
Public method GetLevel Gets a value indicating the level of the SqlHierarchyId node in the hierarchical tree.
Public method GetReparentedValue Gets 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 member GetRoot Gets a value representing the root SqlHierarchyId node of the hierarchy.
Public method GetType (Inherited from Object.)
Public method IsDescendantOf Gets a value indicating whether the SqlHierarchyId node is the descendant of the parent.
Protected method MemberwiseClone (Inherited from Object.)
Public methodStatic member Parse Converts the canonical string representation of a SqlHierarchyId node to a SqlHierarchyId value.
Public method Read Reads from a specified binary reader into a SqlHierarchyId.
Public method ToString Returns the canonical string representation of a SqlHierarchyId node from a SqlHierarchyId value. (Overrides ValueType.ToString().)
Public method Write Writes a SqlHierarchyId to a specified binary writer.

Top

Operators

  Name Description
Public operatorStatic member Equality Evaluates whether two SqlHierarchyId nodes are equal.
Public operatorStatic member GreaterThan Evaluates whether one specified SqlHierarchyId node is greater than another.
Public operatorStatic member GreaterThanOrEqual Evaluates whether one specified SqlHierarchyId node is greater than or equal to another.
Public operatorStatic member Inequality Evaluates whether two SqlHierarchyId nodes are unequal.
Public operatorStatic member LessThan Evaluates whether one specified SqlHierarchyId node is less than another.
Public operatorStatic member LessThanOrEqual Evaluates whether one specified SqlHierarchyId node is less than or equal to another.

Top

Remarks

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.

Examples

DECLARE @employee hierarchyid
SELECT @employee = OrgNode FROM HumanResources.EmployeeDemo
  WHERE LoginID = 'adventure-works\dylan0'

SELECT * FROM HumanResources.EmployeeDemo
WHERE @employee.IsDescendantOf(OrgNode) = 1

Thread Safety

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