with cte as

(select MenuID,MenuDesc,ParentMenu

,Sequence,1 as Lev

from Menus

where ParentMenu is ''

UNION ALL

select m.MenuID,m.MenuDesc,m.ParentMenu

,m.Sequence,Lev+1

from cte

join Menus m

on m.ParentMenu=cte.MenuID

)select MenuID,MenuDesc,ParentMenu,

ROW_NUMBER()OVER(partition by ParentMenu order by Menuid) as [Sequence]

,ROW_NUMBER()over(order by Lev) as [Order]

,Lev

from cte

this puts the correct sequencing,sorry for this, stii I didnt get enough clarity abut order column

I see Order only as increasing number by 1 for each row,which is what produced by above query.

the above used CTE is a recursive common table expressions,which is used for representing hierarchical data like trees,graphs

more information can be found in

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

hope this helps.