Although I agree with Silent Hill at #1, there's is no horror game that can top the
Shalebridge Cradle level in Thief: Deadly Shadows. At least not for me. The burnt down haunted building used to serve as an asylum AND an orphanage at the same time, before it burnt down and became the most disturbing place I've ever seen, crammed with misery and things that can't bear the light of sun.
Thief is not a horror game, yet that single level was more scary then all the other horror games combined. I've played several horror games, but never I had been so terrified as when I played that level. There were moments where I sat in dark corners and not daring to go further, and moments I really screamed in fear. The level is brilliant in every way. It doesn't need hordes of zombies and other creatures to scare you - I was scared even when there wasn't anything going on. The noises you hear, from squeaky doors to the cries of a baby, it won't fail to send chills down your spine and it won't give you a second of rest, nor the illusion of being safe. The fact that you don't have a great arsenal of weapons, nor a character that could survive a meeting with those creatures.
I remember how I hated the darkness when I just got my way into the building, not being able to see anyone or anything. Later I stumbled upon a generator, and with a terrible mechanical scream, it started working and turned on the electrical lamps, replacing darkness with the light I longed for so badly.
And I hated the light, and I would have done anything to make it go away. Whatever was in that building, would have noticed the change, and now there was no darkness I could hide in.
The whole atmosphere, the disturbing elements, I wanted to get out as fast as possible, yet it made me want to explore every corner, every cell, everything, and I did.
I think if a game or a level, designed to scare you, achieves that, it has succeeded.
