Как получить рекурсивный уровень с помощью иерархии SQL Server 2012?

Ваши данные неверны иерачы. Вы можете выполнить свою работу, используя функцию split иrecursive cte

DECLARE @SampleData TABLE
(
   NAMEHID varchar(30)
)

INSERT INTO @SampleData
(
   NAMEHID
)
VALUES
('/2/8/5/'),('/5/11/12/'),('/8/7/9/')

;with temp AS
(
   SELECT * FROM @SampleData sd
   CROSS APPLY
   (
      SELECT * FROM [dbo].[SplitString](STUFF(LEFT(sd.NameHID,LEN(sd.NameHID) - 1),1,1,''),'/')
   ) cr
)
,cte AS
(
   SELECT t.NameHID, t.[Value] , 0 AS Lvl FROM temp t
   WHERE t.Pos = 1
   AND NOT EXISTS ( 
                SELECT * FROM temp t2 
                WHERE  t2.[Value] = t.[Value] AND t2.Pos > 1
               )  -- root ID
   UNION ALL 

   SELECT t2.NameHID, t2.[Value], cte.Lvl + 1 
   FROM  cte 
   INNER JOIN temp t ON cte.[Value] = t.[Value] AND t.Pos = 1
   INNER JOIN temp t2 ON t.NameHID = t2.NameHID AND t2.Pos > 1
)
SELECT cte.[Value] AS NameId, cte.Lvl 
FROM cte 
ORDER BY cte.Lvl, cte.[Value]
OPTION(MAXRECURSION 0)

Функция разделения

CREATE FUNCTION [dbo].[SplitString] (@Text varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
   Select Pos = Row_Number() over (Order By (Select null))
        ,Value = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
   From (Select x = Cast('<x>'+ Replace(@Text,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
   Cross Apply x.nodes('x') AS B(i)
);

Демо-ссылка: http://rextester.com/KPX84657