• The intial issue with the Key Lookup in test case 2 was caused with a poor index. 

    If the following index is added, the CTE is actually outperforming the loops in all cases for me.

    I have used CTEs a lot, and with a little work, you can get them to almost always out perform loops.  Of course there are always the exception.

    CREATE

    INDEX ix_Groups_ParentGroupId_IN_GroupId_GroupName ON dbo.Groups (ParentGroupId) INCLUDE (GroupId, GroupName)