Friday, May 27, 2016

Hierarchical data in SQL Servers

Hierarchical data in SQL Servers was first introduced in SQL Server 2008 by providing the hierarchy data type. This data type allows you to store and query hierarchical data. Hierarchical data is a set of data related to each other by hierarchical relationships. Here are some examples:

  • Parent ->Children->Grand children
  • File system
  • Projects tasks
  • Links between web pages
  • Organizational Structures
You create table in SQL Server with hierarchyID data type to describe data with hierarchical data structure.

Hierarchical Data           Level in Hierarchy
-----------------------------------------------------
John/Mary Parents             /
Their children                    /1/
Their grand children          /1/2/

A slash separates a parents from their children schematically shown for a family relationship:

SQL Server Managemnt Studio supports designing tabels with hierarchical data using hierarchical data type.