Silent Hill is apparently scary to most, but I found it more disturbing then scary.
If you want a scary game, try Penumbra Overture or Penumbra Black Plague (Black Plague is scarier :S)
Penumbra is really cool because you're just an average joe. You aren't no damn Master Chief sporting tons of absurd weapons to just go in and start destroying stuff, Combat isn't as easy and enemies are much more likely to win. In Black Plague you're completely unarmed in fact throughout the game. Anyways, with that the game pressures you to use stealth and hiding capabilities to survive rather then brute force. It also contributed to the horror atmosphere.
I remember my first encounter in Overture. Hiding behind the crate in the pitch darkness, staring into the wall praying that the beast around the corner wasn't about to come to close to me and notice me. Really nerve wracking game, also a fun puzzle game to boot.
