The built-in hierarchyid data type makes it easier to store and query hierarchical data. Hierarchyid is optimized for representing trees, which are the most common type of hierarchical data. Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Hierarchical relationships exist where one item of data is the parent of another item. Examples of the hierarchical data that is commonly stored in databases include the following: An organizational structure, A file system, A set of tasks in a project, A taxonomy of language terms, A graph of links between Web pages.
-Two alternatives to hierarchyid for representing hierarchical data are: Parent/Child and XML. Hierarchyid is generally superior to these alternatives. However, there are specific situations detailed below where the alternatives are likely superior.
.Parent/Child: When using the Parent/Child approach, each row contains a reference to the parent.
.XML: An XML document is a tree, and therefore a single XML data type instance can represent a complete hierarchy. In SQL Server when an XML index is created, hierarchyid values are used internally to represent the position in the hierarchy. Using XML data type can be superior when all the following are true: The complete hierarchy is always stored and retrieved, the data is consumed in XML format by the application and Predicate searches are extremely limited and not performance critical.