The correct type for materialized paths and hierarchies in general is hierarchyid , not varchar . To get consistent ordering and searching with a text type you'd have to ensure all key values have the exact same length by padding them, eg 000014 , 000014.000005 , etc.

hierarchyid is a binary type but allows converting from and to string representations. It can be sorted and indexed and there are built-in functions to determine parents, children, levels etc.

This example from the documentation shows how easy it is to use such paths

Given this table

CREATE TABLE SimpleDemo  
(
    Level hierarchyid NOT NULL,  
    Location nvarchar(30) NOT NULL,  
    LocationType nvarchar(9) NULL
);

and data :

INSERT SimpleDemo  
    VALUES   
('/1/', 'Europe', 'Continent'),  
('/2/', 'South America', 'Continent'),  
('/1/1/', 'France', 'Country'),  
('/1/1/1/', 'Paris', 'City'),  
('/1/2/1/', 'Madrid', 'City'),  
('/1/2/', 'Spain', 'Country'),  
('/3/', 'Antarctica', 'Continent'),  
('/2/1/', 'Brazil', 'Country'),  
('/2/1/1/', 'Brasilia', 'City'),  
('/2/1/2/', 'Bahia', 'State'),  
('/2/1/2/1/', 'Salvador', 'City'),  
('/3/1/', 'McMurdo Station', 'City');

The rows can be orderd with a simple ORDER BY . In this case Level is the name of the hierarchyid column :

SELECT 
    CAST(Level AS nvarchar(100)) AS [Converted Level], 
    *   
FROM SimpleDemo ORDER BY Level;

The expression CAST(Level AS nvarchar(100)) converts the hierarchyid path values to their text equivalent.

The examples in Lesson 2: Create and Manage Data in a Hierarchical Table go into more detail, showing how to add nodes at any level, search for ancestors etc.

In the EmployeeOrg table the OrgNode field is the primary key.

CREATE TABLE HumanResources.EmployeeOrg  
(  
   OrgNode hierarchyid PRIMARY KEY CLUSTERED,  
   OrgLevel AS OrgNode.GetLevel(),  
   EmployeeID int UNIQUE NOT NULL,  
   EmpName varchar(20) NOT NULL,  
   Title varchar(20) NULL  
) ;

When we know the hierarhcyId of an existing employee, we can find all subordinates with the IsDescendantOf function:

DECLARE @CurrentEmployee hierarchyid  

SELECT @CurrentEmployee = OrgNode  
FROM HumanResources.EmployeeOrg  
WHERE EmployeeID = 46 ;  

SELECT *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1 ;

Using the window function ROW_NUMBER to generate an id ordered by path can do the trick :

with cte as (
  select row_number() over(order by path) as id, path
  from mytable 
)
select path
from cte
order by id

Demo here