Unreal - Came packaged OEM with my "new" Voodoo 3 3000 graphics card (WITH GROUND BREAKING AGP SLOT!!!) My draw dropped when i stepped out of the Vortex Rikers (prison ship you start in) and I saw the "massive' enviorment. My dad, who has really been a gamer since the Apple II GS sat and watched me play for like 6 hours.
Half-Life - That initial tram ride with almost zero loading... like a movie intro, only interactive and better.
Command and Conquer - Made me feel like a real soldier for the first time, made me loath the Brotherhood and their underhanded tactics. The intro movie looked ripped right from the news.
Morrowind - that intro, that amazing soundtrack, that first jaw dropping vista, and fully populated towns.
Grand Theft Auto 3 - You mean its all 3d? I can go anywhere? I can steal or shoot anyone? HEAVEN!!!
Fallout 3 - Still playing since day 1 (i have it on both 360 and PC) always finding something new, literally put 300+ hours into the game... ugh im a dork :-)
Operation Flashpoint - That first level was fantastic, especially since every time it played out a little different (up until this point I don't think i ever saw a game without rigidly scripted friendly AI) I remember my squad leader yelping for a split second as his torso was torn away by a stray grenade. Then watching a friendly Cobra helicopter go for a gun run, take just a few too many rounds in the cockpit area and crash into the steeple of the church (which as cool as it was, made me sad to watch such a wonderful machine go up in smoke) not to mention that it never happened to me again :-(
Homeworld - I was only 13-14 when I picked this game out, the cut scenes, dialogue and amazing score of Adagio for Strings (with chorus) still brings tears to my eyes (also because I draw parallels to its brilliant use in Platoon)
Call of Duty - Best Christmas gift ever, and still my favorite call of duty, llke playing through band of brothers, enemy at the gates and the longest day (which I also got for Christmas that year) all great war movies.
Call of Duty Modern Warfare - Actually played this while I was deployed in Iraq (kind of weird playing a game on a laptop that reminds you of the work you did in the morning... like playing the sims....)
oh yeah, even more
System Shock 2 - The first game that genuinely scared me, especially when the hybrids, while hunting for me (hiding behind a crate) are calling our for me to kill them ad end their suffering.
Thief - Just amazing game play, and the ghosts and robots in that game also scared the hell out of me
Mount and Blade - 20 bucks at best buy, probably best "discount game" haven't stopped playing since i got it a year ago, and has an excellent modding community, infinite replay value despite slightly dated graphics.

we are all dorks.
Anyways:
Atlantica Online- BEst F2P game...EVER!!! I love the community and fighting is awesome.
The Orange Box- Wow, it has crashed 5 times now..piece of shit.
Fallout 3- I'm sorry I shot the girl, she had it coming for calling me a name!

Winning Civilization IV on Emperor.

Deus Ex - I ran away from her to avoid a fight, you mean the game knows she's not dead!? And I'll probably end up fighting her again later!?
C&C: Renegade - The interiors of the buildings were HUGE, even a small obelisk of light had three floors! I actually got lost inside of a construction yard. As well as the helicopters flying overhead and the overall feeling that you weren't the only one fighting for your side.
F.E.A.R. - The catwalks and elevator actually frightened me! (still do)

Sins of a Solar Empire made me go WOW! You can zoom in and see the numbers on the side of your ships and then zoom out far enough that they all dissapear. They really did a good job on that game.
Another game that made me go WOW! was Star Wars: Knights of the Old Republic. The plot twists in that game were amazing.

Here's some that I can recall, in no particular order:
Thief: Hiding in terror from a zombie in the abandoned section of The City, wishing for it to shamble away.
System Shock:
I bought this game at the end of 2000. I found it in a bargain bin and thought it was some half-arsed Doom clone. This wasn't helped by the fact that an enemy in one screenshot looked "kinnndaaa" like an archvile from the side, and I had recently read a review of a different game that actually did rip some enemy sprites from Doom. I also vaguely recalled a newspaper review of SS2 (though later I realised the review I recalled was of some other game entirely). It was cheap (just $5 Aus!), so I picked it up. Damn was I ever wrong about this game. It was amazing!
Also - Activating the self-destruct on the reactor, and fighting my way to deck 8, all the while the klaxons are blaring and the whole station is shaking from time to time. Then there was the deck 8 music, with it's strange calmness amid the urgency of getting to the bridge.
System Shock 2: A few moments that stand out -
1) The "reveal": I didn't pick up on the various clues at first, until I got the email from Polito that starts "Do you think this is some kind of game?". A few seconds after the email ended, I twigged that there was some odd electronic sound at the start of the email. I listened again and thought "Oh shit, it's Her". Fast forward to deck 4, and I step into the corridor leading to her office, only to hear the distinct background babbling, my fears confirmed.
2) Working my way through the flooded corridor of MedSci, walking along the upper path. I heard a loud clang behind me, whipped around to see a pipe hybrid that had just missed my back and hit the railing. Definitely increased my appreciation for the Dark Engine. In another game, his pipe would have passed silently through obstacles, and I might have gotten clonked on the head.
3) Walking through engineering, the double doors ahead of me parted, revealing a protocol droid. I brought up my pistol, and quickly tried to cycle round to armor piercers. I accidentally overshot, and had to cycle again. I got them in, as he was closing in, and pulled the trigger. "CHOCK! Weapon jammed." was all that greeted me. I got the hell out of there in a panic, and got the SOB with the shotgun when he caught up.
Goldeneye: I got the game only a couple days after it was released. I spent so much time at first in awe of the graphics. I particularly remember zooming in on a guard with a sniper rifle in the first Surface level. Just seeing him walk around, in silhouette due to the distance, seemed so realistic (at the time of course). Also, like Perfect Dark after it, it has numerous interesting graphical glitches, and various oddities dotted throughout.
Unreal: Looking over the edge of the cliff near the beginning, at the waterfalls, etc., thinking "My 333mhz comp with 128 megs of ram and a Riva TNT2 can do THIS?!?!?"
Deus Ex: Some of the conversations you can have, such as with the bartender in that nightclub in Hong Kong re: communism. Food for thought stuff. Also, on the second playthrough finding loads of extra stuff and various missed clues that now stood out.
Dawn of Darkness: An old total conversion for Quake 2, that sadly never got past episode 1. Fantasy styled with mainly melee weapons and some ranged. Also has a conversation system, shop (IIRC), etc. At one point, I was making my way through a tunnel to sneak into a fortress, turned a corner only to see a massive hammer thing swinging straight at my head (needless to say, SPLAT!). The same dumb expression stayed on my face for at least 30 seconds.
Freespace 2: I always thought there was going to be more than one Sathanas. Didn't lessen the horror when that massive jump portal opened up and I saw this shape coming through, that all too soon became recognizable.

Doom 3: After all the monster-ridden corridors, finally discovering a woman somewhere in an office. I was so hoping to find normal humans, only to look in terror as her head was seperated from her body and turned into one of those bloody flying head-beasties. There was a real feeling of being alone again after I shot them all and stood there staring at the office and the surroundings...
Also Doom 3: entering hell the first time. That was something you worked towards half of the game, epic!
Clive Barkers Undying: The constant feeling of terror and the joy of getting outside during the first part where you face the girl. Really intense going through the manor room by room... And that friggin dragon-cannon that occasionally roars, scaring my hairs off!
Alien vs Predator 2: The absolute joy of playing it against my brother. I was sitting behind him with my laptop while he played on my pc, and as soon as I pressed the pounch-key (being alien of course) I saw him bump up a little in his chair. Even after the 20th time he was still scared, and I loved it. We eventually stopped playing because his stomach hurt too much of tension.
Oblivion: Entering a portal for the first time. Really good.
Chronotrigger: The moment where robo gets his ass kicked by the 6 blue droids, and Lucca gets angry. The bravery, the anger, the payback time. EPIC, and it made me cry every time.
Halo: The moment you meet the flood. I love that. They build it up quite well.
Halo: Racing the warthog in the final part of the game, and getting into the ship with 2 seconds left.
Playing Duke3D the first time against someone else through a serial cable. The feeling of fighting another human, my adrenaline-levels must have reached the moon, I'm glad I didn't get a heart-attack. I did get my ass handed to me on a silver platter, though, but it was terrific
Fallout 2: Finding the bridge-keeper random encounter. Epic!
Vampire (the first game): I somehow grew attached to the relationship between the guy you played and the nun he fell in love with. In the end, when you learned how she manipulated evil from within and was still on my side somehow...
Resident Evil 4 (Wii): Man, a lot of parts of this game are good. I really grew attached to my weapons and ammo. It felt like survival, and it was. Truly masterfull game.
Legend of Zelda - Phantom Hourglass: Finding out how to put the bloody sign on the top screen onto the lower screen (I won't spoil it here :) ).
Grand Theft Auto: Chinatown Wars: Playing the mission with the ambulance, where you have to give the guy in the ambulance hearth massage while being chased by the cops. So cool!
The Typing of the Dead: When some door opens and all zombies have japanese cities for words, aaaaaah!
The typing of the Dead: finishing the game (by dying in the end though) in multiplayer with my brother and going down to ask him what he thought of it. When I entered his room, he was all red-headed as he can't type blind and said: never again, man, never again!
Digger: City of Gold (NES): Finding the City of Gold in the end. I love that game!
Daggerfall: The continuous feeling of going on exploration when you travel the wilderness. Arena did this as well, with all the random stuff. Epic...
Shogo-mad: We went to some Internet cafe with about 15 people, all friends of my neighbour, and I came as well. I didn't know any of them. When playing 15-player deathmatches, I consistently made it to first or second place. Afterwards everyone said: who the hell was aliendna (my old nick) and I made myself known. Made me feel masterfull.
Quake 1 with Painkeep: Deathmatching is just a flurry of hilarious moments with the beartraps, can of beans, gravitywell and other bizarre stuff.
Unreal: There were many places where I just sat and looked around, listening to the soft music, relaxing completely.
Outlaws: rescuing your daughter (wasn't that a goal?) and avenging your wife. At least something like that. The music was great, and the storytelling too.
Diablo 2: The movies between episodes, so very well made, thrilling as Diablo unveils himself and enters hell. Blizzard movies ftw.
Doom 1: Entering the world of fps-games. Just the first experience of running around and shooting stuff. That is indescribable, also since I was quite young back then... ^_^
There are probably many many more. Games can do stuff to my feelings that nothing else can. It's just that they get to you in some special way. Gamer for life, even though I don't have the time anymore...

This.
THIS. THIS. THIS.
Seriously, this is the first thing that popped into my head when I saw this thread. And not to nitpick, but it's a TIE Interceptor that you're flying. :) But yeah, this mission is just about one of the most twisted, but fun missions in the entire game, and it is -the- mission I always used to play when I wanted to show off for other people. Folks who hadn't played the games would watch me dodging all those lasers and just be completely flabbergasted. It was fun as all get out.

GTA IV
I'm still playing it and this game is still giving me WOW moments.
Oblivion
Leaving the prision and seeing Cyrrodil for the first time.
Metal Gear Solid
Battle against Psycho Manthis
Mass Effect
Whole game.
Flashback - Genesis
That game was like a movie on my Genesis System.
Bioshock
The beggining and the "would you kindly" moment.
Starfox
Super Nintendo can do this?
Jedi Outcast
Retrieving the Lightsaber and playing with it for the first time

Wow. You just reminded me of the level in Jedi Academy where one of the ancient Jedi Master tombs is. Jumping across a vast chasm, swinging my dual-edged saber this way and that, pushing Sith off the ledges to a thousand feet of swift death... good times.
Alien Vs. Predator -- The first time I played this was with a buddy of mine in the IT department during lunch hour. Our office was in the basement, of course, so we turned out all the lights, locked the door, and did a network game. We were in separate cubicles so we could hear each other moving and breathing. Totally dark, except for the glow of our monitors. Totally silent except for the click of keyboard keys...
I played an alien, he played a predator. He had me on the run for a while, but I managed to lose him and climb onto the ceiling right above him.
I landed right behind him, lined him up in my jaws and waited for him to turn around ... He turned and just had enough time to see me before: CRUNCH! NOM NOM NOM.
Right then I heard a thump on the other side of the cubicle wall. He'd jumped out of his chair and landed on the floor flat on his ass!
Oh man, we laughed and laughed.

I think the most memorable moment that made me go "Wow" was when I was playing Wasteland back in '88 or '89 and my character had sex with a prostitute. He then get an STD and died.
I think that blows any other moment I've had in a game out of the water, since I was young and impressionable at the time. It really made me consider the dangers of unprotected sex ever since.

I have a couple:
Half Life: Uplink Demo
Played through the aliens enjoying myself thoroughly. I had it on hard, hey I'd done Doom2 on Ultraviolence and I was the living reincarnation of the hellishly hard (ha!) Duke Nukem. Then I walked down a flight of stairs into some kind of large warehouse and met my first two man marine team.
Got UTTERLY cut to pieces. Hence a raised eyebrow and a "Hrmmm, that's odd". Tried it again, ran to a set of crates to do the pop out and shoot "Holy ****! These guys just FLANKED me!!". Next time I retreated back up the stairs to be followed by two grenades. I could not BELIEVE the AI...
The sheer satisfaction of splattering one with the machine gun and seeing his blood splatter on the shipping container behind him was amazing.
And this one, very few of you will understand, but those that do...
I-War
Picked it up in a bargain bin with a shrug. Installed, then the absolutely amazing intro scene (15 MINS LONG!) let me know this was going to be something special. I was at this point a veteran of every X-Wing/Wing Commander and their clones that I could get my hands on. This game put me back in pre-school. It remains the last game I ever played that really took time to master, the last game that made me feel 'there' in a conflict. I miss it dearly, and it's sequel let me down...

That doesn't top the original Strider. You finish the game which was bloody hard anyway and you learn that was a training simulator and now the real mission begins... finish it again and you learn that was a training simulator.... rinse and repeat.

I just remembered another one - the graphics in Neverwinter Nights 2, just after you get out of the house you start in and meet your friends by a bridge, I sat and blinked for a few seconds and thought "this doesn't look like a game, it looks like a painting!"