Just prefix the id's in the TreeOrder column with an appropriate number of zeros's.

Test setup

Declare @table table(SModuleId Int, ModuleId Int, SMName Varchar(100), LinkOrder Int, ParentId int, Level int, Root int, TreeOrder Varchar(Max))

Insert Into @table

Select 1,1,'Academic',3,0,0,1,'1'

Union ALL

Select 8,1,'Content Management',2,1,1,1,'1/8'

Union ALL

Select 15,1,'Content Management For Parent',3,1,1,1,'1/15'

Union ALL

Select 9,1,'Content',1,8,2,1,'1/8/9'

Union ALL

Select 11,1,'Shared File List',2,8,2,1,'1/8/11'

Union ALL

Select 12,1,'Shared File List For Faculty',3,8,2,1,'1/8/12'

Union ALL

Select 16,1,'InContent',1,9,3,1,'1/8/9/16'

Union ALL

Select 2,1,'Administration',2,0,0,2,'2'

Union ALL

Select 13,1,'Alumni List',10,2,1,2,'2/13'

Union ALL

Select 3,1,'Information',4,0,0,3,'3'

Union ALL

Select 4,1,'Personal Information',5,0,0,4,'4'

Union ALL

Select 5,1,'School Setup',1,0,0,5,'5'

Union ALL

Select 6,1,'Sign Out',8,0,0,6,'6'

Union ALL

Select 7,1,'Welcome Page',7,0,0,7,'7'

select * from @table

Solution

;with ModuleTree as

(

select

t.SModuleId, t.ModuleId, t.SMName, t.SModuleId Root,

0 Level, right('00000000' + cast(SModuleId as varchar(max)), 9) TreeOrder

from

@table t

where

Parentid = 0

union all

select

t.SModuleId, t.ModuleId, t.SMName, t.Root,

mt.Level + 1, mt.TreeOrder + '/' + right('000000000' + cast(t.SModuleId as varchar(4)), 9)

from

@table t

join

ModuleTree mt on mt.SModuleId = t.ParentId

)

select

*

from

ModuleTree

order by

TreeOrder