You're 100% correct. How did you know?

I've stared at these execution plans for a few days, they're so simple and I didn't see it. How can you tell?

Without the data, the scenario is worthless. The backup only has one table and one index so you can run/see the query. It's specially created for this exact...

https://www.dropbox.com/s/jd4mgt64zk80nsc/TestScenario.zip here's a backup file.

SELECT LIParent.ItemID as ParentID, LIChild.ItemID as ChildID FROM TItems LIParent

INNER JOIN TItems LIChild ON LIChild.HID.IsDescendantOf(LIParent.HID)=1

WHERE LIParent.NodeType = 0 AND LIChild.NodeType = 1

AND LIParent.TreeID...

Sorry for not posting the exec plan. Here you go https://www.dropbox.com/s/mmjthlfryvsr4l4/Execution%20plan.xml

Yes, the TreeID is a GUID/UNIQUEIDENTIFIER. The IsDescendentOf(..) query is built into the MS HIERARCHYID datatype in SQL...