Specific missions:
All Ghillied Up--Call of Duty 4.
Silent Cartogropher--Halo: Combat Evolved
Although I am not an avid fan of FPSs, these two stick out to me (I was playing through Half Life 2 as well, but a friend is borrowing the Orange Box at the moment...) CoD4's stealth mission was the first time I had played anything in the series, and it sold me on the game. I found myself returning to this one level just to play through the snipping part again (it was suprisingly fun to screw that part up)
When I think of the first Halo game, a few things come to mind: How much I enjoyed the campaign (especially on the first playthrough, when I had no idea that the Flood was coming in and that the robot was an antagonist), my dissapointment with the sequels, hours of multiplayer with my brothers, and storming the beach in the Silent Cartogropher. This is easily my favorite part of the first game's campaign
Moments of Awe:
First time entering the Hyrule Field--Legend of Zelda Ocarina of Time.
Leaving the sewers to see the beautiful, green wilderness--Elder Scrolls IV: Oblivion
Not hard to see what these two have in common. There's nothing quite like the feeling of leaving the confined beggining of a game to see a vast, open area. OoT is up here because 3D was still young when the game was released and I had not seen anything like it beforehand; Oblivion makes it because the moment was just well done: not only is the bright, lush landscape a huge contrast with the dark sewer, but it also a significant change from the game's precesor, Morrowind.
Story Moments:
This one is harder to elaborate on. If you know what I'm referring to, then I don't need a small paragraph to explain my choice; however, if you have not played the game, then I risk spoiling an epic moment. So, I'll just list any games that come to mind in this category with a short, vague reference to the moment that made it a pick for me:
Knights of the Old Republic (the twist)
Metal Gear Solid 3 (the part leading up to the final boss)
Chrono Trigger (End of Ocean Palace, the scene at the top of the mountain, especially if Marle is in your party)