Storing and Retrieving Hierarchical Data in SQL SERVER using HierachyID

HierachyID datatype has been introduced in SQL Server 2008 and is still an available features today. This datatype allows you to store and retrieve data easily. It goes with several built-in CLR functions that will allow you to easily retrieve data based on your specification.






Below scripts creates an employee table with hierarchyID:

CREATE TABLE [dbo].[employee](
[employeeid] [int] IDENTITY(1,1) NOT NULL,
[hid] [hierarchyid] NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[role] [varchar](20) NULL,
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[employeeid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

This script will insert hierarchical data in the newly created table:

INSERT INTO [dbo].[employee]
([hid]
,[lastname]
,[firstname]
,[role])
VALUES
(‘/’,’Morris’,’Philip’,’Owner’),
(‘/1/’,’King’,’Nosmo’,’Sr. Manager’),
(‘/1/1/’,’Walking’,’Jay’,’Manager’),
(‘/1/1/1/’,’Players’,’Andy’,’Sr. web developer’),
(‘/1/1/2/’,’Trinidad’,’Victoria’,’Tester’),
(‘/2/’,’Dela Cruz’,’Juan’,’Sr.Manager’),
(‘/2/1/’,’Mills’,’Richard’,’Manager’),
(‘/2/1/1/’,’Gump’,’Richard’,’Forest’)
GO

You can now start querying for people that reports directly  to Philip Morris by using the getancestor() CLR function. Notice that the parameter is the level of relationship

select * from employee
where hid.GetAncestor(1)=’/’

Results to:

employeeid hid lastname firstname role
22 0x58 King Nosmo Sr. Manager
26 0x68 Dela Cruz Juan Sr.Manager

Hierarchy ID is stored in binary format. You can use the ToString() function to convert it to string.  This query below will return three levels of hierarchy that reports to Philip.

select [hid].ToString() as HID,hid.GetLevel()as Level,[lastname],[firstname],[role] from employee where hid.GetAncestor(1)=’/’ or hid.GetAncestor(2)=’/’ or hid.GetAncestor(3)=’/’
order by 1

This is going to be the result:

HID Level lastname firstname role
/1/ 1 King Nosmo Sr. Manager
/1/1/ 2 Walking Jay Manager
/1/1/1/ 3 Players Andy Sr. web developer
/1/1/2/ 3 Trinidad Victoria Tester
/2/ 1 Dela Cruz Juan Sr.Manager
/2/1/ 2 Mills Richard Manager
/2/1/1/ 3 Gump Richard Forest

You can also make use of getdescendant function to know who is your boss.

select [hid].ToString() as HID,hid.GetLevel()as Level,[lastname],[firstname],[role] from employee where hid.GetDescendant(null,null)=’/1/1/’

This is going to be the result of the previous query:

HID Level lastname firstname role
/1/ 1 King Nosmo Sr. Manager

 




 

Installing SQL Server 2017 on Red Hat Enterprise Linux

In  this new training episode on SQL Server administration. I talked about how to install SQL Server 2017 on Red Hat Enterprise Linux.
 




Here are the steps:

1. Enable the Sql Server Repository

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo

2. run update and install sql server

sudo yum update
sudo yum install -y mssql-server

3. Configure Sql Server

sudo /opt/mssql/bin/mssql-conf setup

4. check server status

systemctl status mssql-server

5. open port 1433

sudo firewall-cmd –zone=public –add-port=1433/tcp –permanent
sudo firewall-cmd –reload