While the Shalebridge Cradle is indeed a masterpiece of unholy terror (and the only time I actually had to stop and have a beer to calm down from a game), the Return to the Cathedral in the original Thief is much more memorable to me.
My experience was fairly unique, though, in that I stumbled upon the Keeper Grotto in The Haunted Cathedral without ever actually making it to the cathedral proper, so when I got to RTC I just stuck the talismans in the door, opened the second set of doors and was greeted by a room full of zombies and these evil skull faced sword wielding cackling monsters taken straight from my nightmares that I'd never seen before. 'Oh fuck that' says I, closing the door immediately. But it's too late, and when I go down a side passage one of the haunts opens the door, charges straight at me and stabs me to death while I'm panicking, fumbling at the keyboard trying to get some holy water arrows to kill it.