HierarchyID Data type - enables a tree structure, modeling an organizational structure, representing of a file system, representing a set of tasks in a project, modeling a graph of links between web pages.
Methods
child.GetAncestors(n) - returns a hierarchyid representing the nth ancestors
parent.GetDescendant(child1, child2) - returns a child node
node.GetLevel() - returns an integer value representing the depth of the node in the tree.
HierarchyID::GetRoot() - returns the root of the herarchy
parent.IsDescendant(child) - is used to find out if a node is descendant of another node
HierarchyID::Parse(input) - converts a string representation of a herarchyid to a herarchyid value
node.Reparent(oldRoot, newRoot)
node.ToString()
create table tblFolders(OrgNode HierarchyID, FolderName VARCHAR(MAX))
--insert root
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(HierarchyID::GetRoot(),'C:\')
--insert second row-child of root folder
DECLARE @Root HierarchyID;
SELECT @Root = HierarchyID::GetRoot() FROM tblFolders;
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Root.GetDescendant(NULL, NULL),'C:\Program Files')
--insert third row-child of root folder
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Root.GetDescendant(CAST('/1/' AS HierarchyID), NULL),'C:\windows')
--insert a child for the 'C:\Program Files' folder
DECLARE @Parent HierarchyID;
SELECT @Parent = CAST('/1/' AS HierarchyID)
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Parent.GetDescendant(NULL, NULL),'C:\Program Files\Microsoft SQL Server')
select OrgNode.ToString(), * from tblFolders
/ C:\
/1/ 0x58 C:\Program Files
/2/ 0x68 C:\windows
/1/1/ 0x5AC0 C:\Program Files\Microsoft SQL Server
CREATE TABLE SimpleDemo (Level hierarchyid NOT NULL, Location nvarchar(30) NOT NULL, LocationType nvarchar(9) NULL);
INSERT SimpleDemo
VALUES ('/1/', 'Europe', 'Continent'), ('/2/', 'South America', 'Continent'), ('/1/1/', 'France', 'Country'),
('/1/1/1/', 'Paris', 'City'), ('/1/2/1/', 'Madrid', 'City'), ('/1/2/', 'Spain', 'Country'),
('/3/', 'Antarctica', 'Continent'), ('/2/1/', 'Brazil', 'Country'), ('/2/1/1/', 'Brasilia', 'City'),
('/2/1/2/', 'Bahia', 'State'), ('/2/1/2/1/', 'Salvador', 'City'), ('/3/1/', 'McMurdo Station', 'City');
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;
INSERT SimpleDemo VALUES ('/1/3/1/', 'Kyoto', 'City'), ('/1/3/1/', 'London', 'City');
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;
INSERT SimpleDemo
VALUES ('/', 'Earth', 'Planet');
exec sp_spaceused 'Sales.Customer'
exec sp_spaceused 'Sales.Store'
MS Dynamics CRM Contributor
