In no particular order, here's a list of some of the most memorable gaming moments I've experienced. Be warned, these are almost all massive spoilers.
While writing this list I was surprised to find that many of my favourite games, games that I love and remember with great affection, left me with no really memorable stand-out scenes that I can add to this list. I believe that those games have great gameplay, and I can remember the overall experience and vague impressions of specific parts of the game, but I suppose that "gameplay" isn't as easy to remember as a single defining narrative moment.
Streets Of Rage 3:
*Finishing the at a friends house on the first try; I finally died on the last boss, but my friend somehow managed to kill the boss on his last life, on our last continue, with one second left on the clock. Epic!
* 10 player death-matches!
* After capturing the "terrorist" leader, he started speaking about how I was being manipulated while agent Navarre repeatedly yelled at me to kill him. I ignored her and kept listening, when suddenly she gunned him down in front of me mid-sentence! In a fit of shocked rage I turned and fired at her... and she died! Up until this point named NPCs had all been invincible, in fact I had occasionally tried to kill one for fun, spending every bit of ammo in every weapon that I had, along with every single explosive I was carrying, to no effect. But not only did she die this time, the game actually went with it. Not only was that the first time I had ever seen an NPC seem to act dynamically rather than obviously reacting to a player prompt of some kind, it was probably the first, and perhaps the last, time that I made a real decision in a game that wasn't simply choosing between two menu options or obvious paths that the game had clearly laid out, and instead of breaking it had been OK with it.
Half Life 2:
* Feeling helpless and desperate as I watched Alyx getting hustled away by Combine soldiers; that was probably the first a game had managed to get me to care about an NPC.
* Feeling truly evil as I tossed enemies around like rag dolls with the supercharged gravity gun in the final level.
The Prince of Persia - The Sands of Time:
* Being treated to a view of the entire kingdom as I climbed up a cliffside
* Narrating the entire tale to Princess Farah in the past.
* Using the power of the dagger to steal a kiss from the princess before finally disappearing into the night.
The Prince of Persia - The Warrior Within:
* Trying desperately to escape the invincible Dahaka.
* Donning the Mask of the Wraith and viewing events a second time from a new perspective, only to end by paradoxically killing my past self rather than dying in his (my?) place.
The Prince of Persia - The Two Thrones:
* Narrating the entire tale to Princess Farah once again, finally tying back to the start of the whole trilogy.
* Seeing the changed statue and realising I may have just changed the entire course of history.
* Stepping through a time rift back into the present only to see a flock of glowing jellyfish-like creatures float across the sky and wondering to myself: "What the hell am I doing to the world?".
God Of War:
* Seeing Ares for the first time.
* Seeing the Titan carrying the maze.
* Staring down the giant chain and seeing the Titan's arm and head far below.
* Clawing my way out of Hell and emerging from hole the old man was digging.
* Lifting the sword-shaped bridge and using it as a weapon against Ares.
God Of War II:
* Fighting the giant statue.
* Protecting the sword from the minions of the Oracles while my past self fights Ares in the background.
God of War III:
* Watching myself kill Helios... from his perspective. That was brutal!
* Figuring out how to get the final few jigsaw pieces in the first world by using the jigsaw itself.
* That one puzzle with the two doors and only one key, where rewinding time relocks the first door but doesn't reform the key. I still have no idea how I solved that one. It was the last puzzle in the entire game I hadn't solved, and I spent ages trying to figure out how to coordinate my actions with my shadow in such a way as to be able to unlock both doors, losing patience and flailing around, only to suddenly see both doors open. I'm still don't know how it happened.
* Watching the last level play out in reverse, only to remember that's the right way round - total brain bender!
* Unleashing the Flood - damn that was tense!.
* Driving the Warthog down a narrow alleyway as the clock counts down the seconds until the whole Halo explodes.
* Uh... not much I guess. Well, I suppose spending many, many hours trying to get through the first damn level on Legendary, only to finally give up.
* Driving the Warthog across a strangely surreal artificial landscape as the clock counted down the seconds until the whole planet explodes.
* Watching the after-credits cutscene of Cortana and Master Chief. Still one of my favourite things ever.
Call of Duty - Modern Warfare:
* Watching events unfold through the eyes of a deposed dictator.
* Hiding in the long grass in a ghillie suit as an entire army passed within inches of me.
* Taking down the last boss with that final shot.
Call of Duty - Black Ops:
* Watching numbers bleed off the walls as I stumbled around in a drug-fuelled daze.
* Realizing Elena was not just an annoying damsel in distress, but a gutsy, intelligent, capable, likeable human being.
* Realising that Sully wasn't dead or a traitor, but a loyal friend.
* Riding alongside the Nomads, taking down a convoy of trucks.
* Exploring an amazing ancient Arab palace.
* Dodging flying furniture as the building fell around me in a surreal dream-scene, until I was left standing on the single remaining piece of floor, inextricably left floating in mid air.
* Dodging giant microscopic fleas. Trippy!
* Realizing that my brother would actually live or die depending on how fast I was. Not many games actually let such a big thing hinge on whether the player actually succeeds or not.
* The surreal nightmare sequences; running down a corridor only to see the doorway at the end seem to grow farther away, following trails of blood in complete darkness while a baby cries... not stuff you forget very easily.
Alan Wake American Nightmare:
* Desperately reloading and trying to figure out where the nearest ammo cache is while back-pedalling from over a dozen Taken as they try flank me, only for Alan to turn his head to look off-screen at the nearest Taken; desperately hitting the dodge button and watching as Alan ducks - in glorious slow motion - under a scythe that comes swinging from off-screen. Damn, that game got my blood pumping!