Declare @I_D Int  --входной номер ветки
 
SET NOCOUNT ON
Declare @Level Int
Declare @LastCount Int

Create Table #Tmp (I_D Int, Level Int)
 --если количество нодов в ветках может быть большим, то я обычно добавляю еще и индекс:
 
CREATE  INDEX [IX_TMP] ON #Tmp ([I_D], [Level]) 

Set @Level =  1 
Insert Into #Tmp (I_D, Level) Values (@I_D, @Level)
Set @LastCount = @@ROWCOUNT
While @LastCount >  0  
Begin
  Set @Level = @Level +  1 
  Insert Into #Tmp (I_D, Level) 
  Select ID_MATERIAL,  @Level 
  From tbl_Tree Inner Join #Tmp On #Tmp.I_D = tbl_Tree.PARENT_ID 
  Where #Tmp.Level = @Level  
  Set @LastCount = @@ROWCOUNT
End
SET NOCOUNT OFF
Select tbl_Tree.* 
From tbl_Tree Inner Join #Tmp On #Tmp.I_D = tbl_Tree.PARENT_ID 
Order By #Tmp.Level, tbl_Tree.Name