Q:

Implementing a Multi-Tree in SQL Server similar to one that exists in Oracle

Beginner to MSSQL coding, I was asked to implement a complete tree structure, one that apparently already exists in oracle. Unsure what to do. 
Implemented a simple example dataset but unsure what else a node needs for transversing and insertions.
Create table HeirachTree(
    NodeID int not null primary key,
    NodeName nvarchar(100) not null,
    NodeParentID int,
    NodeNext int,
    )

  NodeStructure ([Node_ID], [Node_Name], [Node_Parent_ID], [Node_Next], Node_Depth)
  AS
  (
    SELECT [NodeID], [NodeName], [NodeParentID], [NodeNext], 0
    FROM [dbo].[HeirachTree]
    WHERE [NodeParentID] IS NULL    --Select all root nodes.
    UNION ALL                       
    SELECT t.[NodeID], t.[NodeName], t.[NodeParentID], t.[NodeNext], 
      s.Node_Depth + 1              
    FROM [dbo].[HeirachTree] t
      INNER JOIN NodeStructure s
        ON t.[NodeParentID] = s.[Node_ID]
  )
SELECT                              
  Node_Name, 
  Node_Depth,
  (SELECT [NodeNext] FROM [dbo].[HeirachTree]
    WHERE [NodeNext] = NodeStructure.[Node_ID]+1) AS NextNode,
  (SELECT [NodeName] FROM [dbo].[HeirachTree] 
    WHERE [NodeID] = NodeStructure.[Node_Parent_ID]) AS ParentNode
FROM  NodeStructure
ORDER BY Node_Depth, Node_Parent_ID 

So, found an example and had the depth calculated using a cte; for easier query on using it as a constraint. 
Assumed a BFS and thought i needed a reference to the next child too. So do i map the NextID to ID using some kind of Key or joins or...? Is it even necessary? 
How can i insert a specific node somewhere in the middle of my tree? 
Something like using a Select over 
CASE NextID WHEN NULL then NodeDepth+=1 ELSE NodeDepth 
Or is the train of thought in a completely wrong direction?

A:

There is a rarely used concept existing for exactly this: The HIERARCHYID. This data type knows about 10 native methods like GetDescendant or GetReparentedValue
This example is taken unchanged from the documentation of GetReparantedValue and seems to demonstrate your needs.
DECLARE @SubjectEmployee hierarchyid , @OldParent hierarchyid, @NewParent hierarchyid  
SELECT @SubjectEmployee = OrgNode FROM HumanResources.EmployeeDemo  
  WHERE LoginID = 'adventure-works\gail0' ; -- Node /1/1/2/  
SELECT @OldParent = OrgNode FROM HumanResources.EmployeeDemo  
  WHERE LoginID = 'adventure-works\roberto0' ; -- Node /1/1/  
SELECT @NewParent = OrgNode FROM HumanResources.EmployeeDemo  
  WHERE LoginID = 'adventure-works\wanida0' ; -- Node /2/3/  

UPDATE HumanResources.EmployeeDemo  
SET OrgNode = @SubjectEmployee. GetReparentedValue(@OldParent, @NewParent)   
WHERE OrgNode = @SubjectEmployee ;  

SELECT OrgNode.ToString() AS Current_OrgNode_AS_Text,   
*  
FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\gail0' ; -- Now node /2/3/2/  

Look at the various native methods...

