When I was a kid, I'd of said heroic deaths. Death should be heroic...if you don't take it seriously.
You can't rage against the coming of the night if the coming looks cool. And I don't want to be a death-fetishizing Klingon. The best part of the the fetish is the reveling at how cool you go out, but if you actually go out, you won't do any reveling! As that shrew Ayn Rand said, to you, you don't experience what it is to be dead (in an afterlife or moment of understanding) the whole universe just ends. You go back into that emptiness from before you were born that you have no notion of.
Show death as the absurdity it is in this tragic universe and let us rail against it.
One day humans may become immortal. If we don't all die, it's probably just a matter of time, given our unceasing thirst for knowledge and technological development. It may take longer...maybe nuclear war will delay it for a thousand years or fifty. But if we do have the time to make it happen, we will. We'll store our minds in cyberspace, or clone our bodies, or find ways of regenerate our cells, or what have you. I think it sucks that I won't be around to see it. And if I'm gonna have to die because was born at the wrong time...and if we do all die, some other sentient beings'll come along and make it happen, if they haven't already somewhere out there in the infinite universe. But if I'm going to have to die, I want it shown for how fucked up it was.
I'd take a bullet for the president or be the last man on the bridge so the crew could get away or what have you. And so would countless people and I want that shown too. But damn it most people die just "average" deaths and that's how I want most deaths shown. They all suck, even the "great" ones. The really shitty deaths, the ones that are ugly (actually ugly, not cool ugly) and stupid and funny in their ridiculousness should also be shown, but in proper proportion - I sometimes think if you let an "artiste" show death, 9 out of ten of them would fit into this last category. ...Everyone would be dying of stray bullets to the head, the protagonists taking drags on their cigarettes and pissing about how shitty life is. Fuck you, asshole, get a clue.
...Forgive my ranting - I didn't set out to. I think the most important thing I want out of deaths of characters is that they're done well. Whether the deaths are heroic, mundane, or revolting, they should be done well, artistically. But I suppose they always try to do things well (episode, movie, book, etc) and some just turn out poorly.
Kirk's death was especially poor because he was so iconic a larger-than-life character that got a lackluster death. Not to be confused with a realistic one because how many people die plummeting on a bridge, a century after their natural time, having just saved 240,000,000 people?