Call of Duty was ALWAYS about the shock value, they went too low on this one to that it makes no sense whats so ever.

SPOILERS.

its like... you get shot, Random freaking shootings, getting betrayed , a random freaking nuke explodes in space, terrorists attacking an airport, washinton DC gets bombed, your teammates betray you again, you get shot again.

they are really trying hard to shock you, but it just ridiculous.

from your experience, i think you have started playing games late im i right?

Well Ive been playing videogames since Snes days but I got an xbox 360 a week and a half ago and now I began playing my first High quality shooters. 

the SNES had alot of breathtaking scenes from games like final fantasy? >.>

thanks for the white out planing on picking this up later.

This (Valkiria Chronicles)

Also (some SPOILERS):

-if we are talking MW, I think COD4 one better: the nuke scene.

-MGS: the microwave tunel

-Warcraft 3, the death of Grom :(

-FFT: the whole first chapter (f_cking Algus)

-God of War: the ending of the fight where Kratos has to protect his family. After that, I really wanted to kill Ares.

- Uncharted 2: the village scene, everything after fight with Lazarevic.

-Resident Evil 4: the scene with helicopter. Must have been one of the most exciting moments I've played.

I might be forgetting several others, but the winner form me is FFVI: the opera, the solitary island, the siege of Doma castle, the dream of Cyan, the sidestory of Gau, the killing of Terra's parents, Kefka killing Gestahl, Kefka killing Leo...too many.

Mine aren't even "scenes," but these are all memorable moments for me. My list is very eclectic.

- The Kingdom Hearts 2 intro. It really amazes me every time.
- Jumping off of a skyscraper in Prototype. I'm not even afraid of heights but I get this insane sensation when he's falling. Weird.
- When I first went through a portal in Portal. My mind was literally blown.
- I'll never forget playing Left for Dead with surround sound headphones on!

As a kid, Ocarina of Time and 007: Goldeneye were pretty 'breathtaking' just because of the technology involved. Mostly, pushing games into 3D.

But as for games where I truly felt such an experience:

Dragon Quest VIII: That first time I walked out into the overworld and heard the combination of the overworld theme and saw the expanse of the world stretching out forever is an experience I will never forget. And I never got over for the 160 hours I played the game.

Resident Evil Remake: Much of the game amazed me graphically when it came out. But when I first walked out into that graveyard on my way to Lisa's house and saw the rolling fog through the brush, the moonlight and the shadows...it was the first time I truly saw a game I felt looked 'realistic'.

Edit: Now updated with video!  Fancy.

Dragon Quest VIII - Exploring the Overworld (Though its using DQII Music)

Resident Evil Remake - Jill on the path to the Cabin

I thought is made perfect sense. Except they didn't tell it well.

every game where x heroe sacrifices himself

There are so many I don't even know where to begin.

CoD4 nuke, of course, is one. But that's all I can think of to mention right now. If I were to think about it properly I'd probably come up with like 50 different noteworthy ones. I might post some more ones later, but somehow I doubt it.