Optimizing removal of duplicate rows from recursive CTE query
How about this? Kind of a hack - but row_number should be quicker than distinct.

;WITH Lineage AS
(
SELECT
    Name
   ,CASE WHEN ROW_NUMBER() OVER (PARTITION BY ParentName ORDER BY ParentName) &gt; 1 
         THEN NULL 
         ELSE ParentName END AS ParentNameToJoin
   ,ParentName AS ParentNameToDisplay 
FROM
    #Project
WHERE
    Name in ('C1','C2')
UNION ALL
SELECT
    p.Name
   ,CASE WHEN ROW_NUMBER() OVER (PARTITION BY p.ParentName ORDER BY p.ParentName) &gt; 1 
         THEN NULL 
         ELSE ParentName END AS ParentNameToJoin
   ,p.ParentName 
FROM
    #Project as p
INNER JOIN
    Lineage ON p.Name = Lineage.ParentNameToJoin
)
SELECT
    Name
   ,ParentNameToDisplay
FROM
    lineage