Knights, samurai and other warrior elites know that the point of life is not just to live well, but to die well. Adventure games, though, typically give no rewards in themselves for a good death. Nevertheless, the recent heroic self-sacrifice of one of my campaign's characters gives room to illustrate how death may be given its consolations, both through mechanics and gameplay.
On the other hand, character death really sucks. I'll leave it to Trollsmyth to spell out some reasons why, but it's no fluke that it's essentially vanished from commercial online gaming, and my Niecely Informant doesn't see much call for it in her own freeform online endeavors, either. For people who don't have a lot of time to spend playing, the grind back up to reasonable power levels can be very demoralizing. "Old school hardcore" is all very well and good, but it's a philosophy devised in an age when people gamed two weekday nights and all weekend.
One way I cushion the sting of death is to award half the old character's experience points to any new character the player rolls up. In fact, if I'd set out thinking from the start, I'd use this rule to separate good deaths from bad - a heroic or selfless death would allow a respawn at 2/3 xp instead of 1/2 xp. Interestingly enough, Ephemera's player (my wife) has rolled up another wizard, even down to the same main Sleep spell, so it's not too much trouble to pass the new character off as a student from the same school ...
Another way is the aforementioned negative HP tables, that dole out the threat of maiming or incapacitation as a less serious gradation of death. Sometimes the threat of consequences is enough. This goes even for systems like 2nd edition with its highly generous death's door rules - if the GM is plotting the campaign world well enough, failure in a mission will feel like the loss of a limb, as the consequences on the PC's allies and acquaintances make themselves clear.
Barring any further mechanical tweaks, this kind of social GMing can go some ways to showing the consequences of a good death. Ephemera's sacrifice has so impressed the man she saved, Fergus, that he has foresworn his bandit ways and with a few of his fellows has been sworn into the Trossley Village Guard and given domain over the newly opened east gate (facing the Castle). The bandits slept by her spell were trussed by the survivors and taken to justice - all are due to be hanged, even the Young Fergus, after a scene in which he rebuffed his older self. And Ephemera's mentor in Utherton, Joya, has been profoundly affected and is showing the party survivors a level of help she would have only given her student.
Ephemera has been given burial in the tomb-wall that lies in the garden in the back of the Temple of St. Hermas. Who knows? It may be that her name and legend will be enough to secure the party a more favored status in the town, as everyone awaits the tide of hungry treasure seekers ....
So how do you handle character death? Are the adventurers just leaves on the wind, rootless treasure seekers to be cut down and spring up again? Or does their death carry some meaning ... and in what way?