This is very dependent upon the setting. In one campaign that I play in, admittedly heavily modified from the "typical" setting, the DM does not allow for raise dead or similar spells. In his campaign, death is the end of a character. That's it.
And, in that campaign, we all design characters for the long haul. Campaign arcs last for years (decades, really, two of the players in that group have been playing the same characters since about 1988). Advancement is SLOW (as you'd expect, given that it's taken us 20 years), but the campaign is rich in detail and a blast to play in.
In the campaign I DM, death is common. Characters drop like flies (or, rather, like the first five minutes of a Paranoia session), though we use hero points and the well-timed use of a hero point will auto-stabilize a character (that has died from hp loss) regardless of the means of death.
In the campaign I run, there are all kinds of tales of cool deaths. Players wear their experiences in the afterlife (I have experiences between the time a character dies and is revived) like badges. In my friend's campaign, PC deaths are exceeding rare (I've lost one character, on purpose, in 20+ years). In his campaign, PC death isn't fun in the same way that other things are fun. PC death is to be avoided.
In my campaign, death is fun.
However, there's death and then there's death. In video games, it's one thing to get sniped. No big deal, you re-spawn and go at it again. In D&D, deaths are a little more like in real life (though admittedly still very different). I'd wager that the designers want to carefully negotiate this territory because of the not-too-subtle comparisons we all make between characters and real people.
Dave