I think it does return the ragged hierarchy like your example. It tooks 22ms (definitely less than 1s) to return 72 rows.

Screeshot here

http://tinyurl.com/be6yqxp

or

https://docs.google.com/file/d/0B8BX62MoYSHGRjd3RWo2Q0pERFU/edit

Oops, I should have say "I think this is a better way...." You can find your proof easily if you know copy and paste. But SQL newbie would have know...

Hi someone ask me the same question: How to flatten a hierarchy self-referencing table, Google it and your thread and my first thought is Recursive CTE too, but then I'm...

Thanks Peter, thats a really good advice and hopefully I can do that for another exam cause I almost run out of time for couple of the case study in...

Took & passed the exam this morning for almost 3 hours and I'm feeling REALLY exhausted right now. I passed it with 820 and wished MS can release the answer...