For me, the entire Silent Hill series (yes, even the arcade rail shooter) was scary. Amnesia: The Dark Descent is up there with Silent Hill, followed closely behind by F.E.A.R & Dead Space.
But for something completely different, how would you like to be scared by SNES-type games? Yume Nikki can only be described as EarthBound meets Silent Hill. It is quite creepy. Then there's .flow, the spiritual successor to Yume Nikki. It's worse than Yume Nikki. Go google to have fun tonight.
Also, not sure if Visual Novels count as games, but Higurashi no Naku Koro Ni was one hell of a nightmare.