Clive Barker's Undying and the Blair With Project (the game, though the movie did that too)

Not in any order:
System Shock 2
Call of Cthulhu: Dark Corners of the Earth
STALKER: Shadow of Chernobyl
Painkiller
Alien v. Predator 1 & 2
Silent Hill 2
Bioshock
Indigo Prophecy
Penumbra Overture
DOOM 1, 2, & 3
Hexen 2
Half-life 2
The Thing
F.E.A.R.
(on console: Fatal Frame)
X-Com did a great job of building suspense, but I wouldn't really call it scary. I remember the way it sucked me into its world, but I never actually jumped or felt like somebody was behind me the way I have in other games.
EDIT: Oh yeah, and to the person who was scared by Myst: you're not alone. It had a creepy atmosphere that made you feel like somebody might jump out at you at any moment.

The very first time I played it, I had my PC set up in a small, dark basement room with surround sound speakers mounted on the walls all around me. When the furnace in the next room turned on unexpectedly, I nearly crapped myself. Now when I play it, its almost funny to think about how it sucked me in.

System Shock 2 just had that certain something - perfect build up, watching a crewmember being chased down a corridor, when you can't get access to it and before you have had any sort of combat, then 5 minutes later realising you're now in the same corridor....
Doom3 - Gave up on it in the end because of the Idiotic Spawning, but it does atmosphere very well indeed..
Thief3 - The Cradle. Like has already been mentioned, 'nuff said.
Thief1 - Lots of it! First 'Creep em up', once the mechanics were worked out, had a great time fiddling about in the first castle level, finding different ways to steal that sceptre..then suddenly, next level youre underground, theres zombies etc , and it all gets a bit wobbly....
CB's Undying - again, build up was everything, although there are plenty of other moments once the battle was joined..
For me, to have a chance of scaring / jolting / shocking you, a game has to be First Person. So therefore sadly I can't include The Thing, although i was Really looking forward to it when it came out, as its one of my all time favoutrite films. The game turned out a bit poo, frankly..

System Shock two gave me the creeps.
Sadly, Thief-3... There was too much hype around Shalebridge that ruined what should of been a good experience. While I did not listen to exactly 'what' was scarey, when many people recommending the game keep on mention the level, the actual playthru is disappointing :(

Well, there's games that made me jump, like Aliens Versus Predator, but games that scared me, as in a prolonged state of emotional fear....?
Scratches is probably the most recent game to do that, for me.

I'd have to say, for me the scariest game, well maybe not scary as in "I want to stop playing" but scary as in creepy and unique, would have to be Sanitarium.
The game is just a creepy atmospheric ride throughout the whole thing. I was especially creeped out by the second town, with all the little deformed children and their "friend" in the wheelbarrow/one you had to find or whatever. That area is stuck in my head and I'll always remember the game.
After that I'd say Silent Hill was the next one, again mainly for the same reasons. The games both exude a creepy atmosphere that likes to fuck with you and it doesn't rely on cheap "jump out at you" scare tactics with the likles of Resident evil and other games do rather it lets the mood set in and keeps you in that state for a prolonged amount of time.

Call of Cthulhu and Diablo (I was about 10 when I played it) are the only games I can think of

system shock 2
Bioshock had a few spooky/eerie parts too.

For an interesting mix of genuine fear/anxiety and "BOO!" scares: Rebellion's Aliens Vs. Predator (PC, not Jaguar), the Colonial Marine campaign. Monolith's sequel was much more of a standard FPS, but the first one had me genuinely on the edge of my seat, creeping forward, and spinning and backpedaling frantically from an opening door as it set off the "BRE-DEEP!" of the motion sensor. And then there was the face-huggers. The way they attacked was pure "BOO!" (a sudden loud hair raising sound effect and your screen covered with legs and an alien ovipositor), to the point that before long just the sound of one scuttling around was enough to have you throwing explosives or profligately spending flamethrower fuel just to kill the damn things (HL2's poison headcrabs had a similar, if lesser, effect).
For pure adrenaline rush: Call of Cthulhu: Dark Corner Of The Earth's escape sequence in which the inhabitants of Innsmouth burst into your hotel room.
Unfortunately in my experience, these are generally "First playthough only" scares.

Blood! It is silly and campy, but for some reason the atmosphere was disturbing. Every time there is anything on the Internet about scary games, they never mention Blood. Maybe I am the only person to find it scary, but there is something about it. I still play it with dosbox. It is just about my favorite linear game. The sequel was a disappointment to me. It just didn't have the same intensity.
Everyone always mentions System Shock 2, but, though it is a great game with some disturbing parts, I never found it very frightening.
Blood doesn't make me jump, but the imagery is so bizarre and creepy. I think it should be available here at gog.com, and hopefully it eventually will.
Now you may all make fun of me for finding a silly game like Blood scary.

I had forgotten about that one... Yes, it does it rather well.

EPT: The Plus Sign.
I was terrified.

Plenty of games were genuinely creepy, like Condemned or System Shock 2 but for actual scares? Well that one mission in "Vampire - Bloodlines" already got mentioned. Also "Project Zero" on the PS2 (known as Fatal Frame in North America).