CLR data type ,hierarchyid

The heirarchyid is a system data type available in SQL Server 2008 to represent hierarchies. It is based on a CLR data type, but is always available, whether the CLR is enabled or not.

The hierarchyid data type is a variable length, system data type. Use hierarchyid to represent position in a hierarchy. A column of type hierarchyid does not automatically represent a tree. It is up to the application to generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in the values.

  • /
  • /1/
  • /0.3.-7/
  • /1/3/
  • /0.1/0.2/
  • for more detail


    2 thoughts on “CLR data type ,hierarchyid

    1. Hi ,
      I recently ran into an issue of using the hierarchy ID in C#. And a workaround was to read the HierarchyID as a string and then use that.
      For example,
      Select hID.tostring() as HierarchyPath from HierarchyTable

      –hID is of sql type HierarchyID

      This can be fetched into C# typically using DataTable or DataSet types.

      And then use a statement like
      update HierarchyTable SET attribute=’value’ where HierarchyID.IsDescendantOf(‘/path/in/hierarchy’)

      where /path/in/hierarchy is what the earlier select statement fetched in the HierarchyPath column.

      But it’s in my wishlist that the CLR should have a datatype for hierarchyID.


    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out /  Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )


    Connecting to %s