I really liked the Assassin's Creed sequence that plays right before the main menu, where Altair is set to assassinate someone, and he escapes in a crowd of monks. That's probably the most exciting.
I also really enjoyed the sequence before the main menu in Fallout 3 where you start inside a bus, and slowly, the camera pans backward, showing the destruction of the wasteland with "I Don't Want to Set the World on Fire" playing on the broken stereo. It sets the mood so well, I get chills everytime I watch it. That would probably be the most effective.