Hierarchical CTE with additional sort column per level

CTEs are a bit new to me so I am hoping someone can help with the following one which I wrote that will take a category table and build a hierarchy out of it for display. I know this kind of thing is asked all the time, but I think my situation with the sorting makes it a bit unique.

I would expect a number of suggestions to use HierarchyID, but sadly that is not an option for a long list of reasons that are not relevant here. The solution I have come up with though works and gives me the data I expect, but I am wondering if there is a better/more elegant way of accomplishing this.

The basic requirements are as follows:

1. Categories can have an unlimited number of children
2. Categories can be an unlimited number of levels deep
3. Categories with the same parent will be sorted based on a "sort" field. If one is not specified (default is 0) or is the same as another sibling category, it will sorted alphabetically.

Table Definition:

CREATE TABLE [dbo].[TreeTest]
(
    [id] [int] NOT NULL,
    [parent] [int] NULL,
    [title] [varchar](50) NOT NULL,
    [sort] [int] NOT NULL
)
GO

ALTER TABLE [dbo].[TreeTest] ADD  CONSTRAINT [DF_TreeTest_sort]  DEFAULT ((0)) FOR [sort]
GO

Insert statements:

INSERT TreeTest(id,parent,title,sort) VALUES('1',NULL,'Parent 1','0')
INSERT TreeTest(id,parent,title,sort) VALUES('2',NULL,'Parent 2','0')
INSERT TreeTest(id,parent,title,sort) VALUES('3',NULL,'Parent 3','2')
INSERT TreeTest(id,parent,title,sort) VALUES('4',NULL,'Parent 4','1')
INSERT TreeTest(id,parent,title,sort) VALUES('5','1','Child 1a','0')
INSERT TreeTest(id,parent,title,sort) VALUES('6','2','Child 2a','0')
INSERT TreeTest(id,parent,title,sort) VALUES('7','3','Child 3a','0')
INSERT TreeTest(id,parent,title,sort) VALUES('8','1','Child 1b','1')
INSERT TreeTest(id,parent,title,sort) VALUES('9','1','Child 1c','2')
INSERT TreeTest(id,parent,title,sort) VALUES('10','1','Child 1d','1')
INSERT TreeTest(id,parent,title,sort) VALUES('11','6','Child 2a 1','0')
INSERT TreeTest(id,parent,title,sort) VALUES('12','6','Child 2a 2','1')
INSERT TreeTest(id,parent,title,sort) VALUES('13','6','Child 2a 3','0')
INSERT TreeTest(id,parent,title,sort) VALUES('14','6','Child 2a 4','2')

CTE:

WITH TreeList (id, parent, title, sort, title_path, level_id, level_id_path) as
(
    SELECT p.id, 
           p.parent, 
           p.title, 
           p.sort,
           CONVERT(nvarchar(max), p.title), 
           ROW_NUMBER() OVER(PARTITION BY parent ORDER BY p.sort, p.title), 
           CAST(ROW_NUMBER() OVER(PARTITION BY parent ORDER BY p.sort) AS varchar(max))
        FROM TreeTest p
        WHERE p.parent is null
    UNION ALL
    SELECT c.id, 
           c.parent, 
           c.title, 
           c.sort,
           r.title_path + '/' + c.title, 
           ROW_NUMBER() OVER(PARTITION BY c.parent ORDER BY c.sort, c.title), 
           CONVERT(varchar(max), r.level_id_path + '.' + CAST(ROW_NUMBER() OVER(PARTITION BY c.parent ORDER BY c.sort, c.title) AS VARCHAR))
        FROM TreeTest AS c
        INNER JOIN treelist AS r
            ON c.parent = r.id
)
SELECT *
FROM TreeList
ORDER BY level_id_path

Output (I figured an image was the easiest way to show the output)

Again, this works a开发者_运维技巧ccording to the specifications I have, but I'm not sure about the efficiency and whether or not there is a better way to do this. When I look at the execution plan for this, it seems like the most expensive piece is the sorting/index scanning, but this seems expected given the lack of indexes in this example. If anyone has any input it would be greatly appreciated.

---

order by case when parent=0 then parentid else id end

here id means primary key on table

---

That recursive CTE is pretty much the cleanest option in SQL Server. If it were Oracle you could use CONNECT BY but there is no equivalent in SQL Server given you've already stated you can't use a HierarchyID.