I think you're onto something there - the sudden death of a major character can be real shock and a great twist, whether it happens in the middle of a story or at the end. It really helps to highlight the arbitrary, sudden nature of violence, and to remind the player that such a fate can befall anyone. This has been done well in movies: in
Pulp Fiction, when
(SPOILER)Vinnie is shot by Butch, and the audience only really has a moment to register the fact that a leading character has been killed, and
The Departed, in which
(SPOILER)most of the main characters are executed with unceremonies, wanton callousness at the end.
I'd guess that
(SPOILER)Galuf's death in FFV comes fairly close to this, but he was never
the main character.