Dead space is not scary at all.

When it comes to a scary atmosphere, I'll give props to Doom 3. I think it's because when I played dead space, what first came into my mind is : this game's atmosphere is ripping off Doom 3, except i couldn't feel it... if ya know what i mean. RE4 is not scary it was fun, but still kept a horror feel even though you know nothing will scare you.

Edit: And Japanese ghost little girls are not scary at all...

Scary games?  Hmm.  Maybe Silent Hill 2.  Jumps, etc. are fun and for me not remotely scary.  Something like playing as a Marine in an Alien vs Predator game can be very tense, but not really scary.

But Silent Hill 2, evokes such a sense of mortal dread, and fear for what the journey will reveal, that I guess it's the closest of any game I've played.

I do think games can be great at tension.  Fatal Frame, Siren titles, Dead Space and of course games like Resident Evil and Silent Hill can all evoke very tense situations, as can titles like FEAR.

But for me scary means genuine sense of fear, and an adult fear at that.  At only Silent Hill 2 has come close as a game for me.

Dead Space was heaps scary and it perfected everything Resident Evil attempted to do and it added it's own innovative touches that made the game so much better than your standard horror game. No one should play the game on easy, I did and then went and played it on hard, totally different game.

Siren: Blood Curse should be mentioned more often, it's the only game that I actually stopped playing because I was being to creeped out and my heart stopped a couple of times.

The Condemned and F.E.A.R. series are both really good and they both bring plenty of horror to the plate.