The first question that must be asked is, "What is the purpose of the game?" This is a question that has to be answered by each gaming group, as it greatly determines the style of game being played. For example, a group that's just interested in a combat-focused dungeon crawl is very different than one interested in roleplay-heavy character development and strong plot elements. The first group is generally not very attached to their characters except in an abstract way (a means to getting loot, leveling up, and other achievements based on game mechanics). However, more story-driven players are the ones who suffer much more from character death.
I tend to compare RPG heroes to comic book superheroes. How often do comic heroes actually die? And when that does happen, it's almost solely as a plot device to further the story. In my opinion, killing a PC is essentially forcing an end to the story the player was telling, regardless of the impact to the larger plot. The key idea there is that of "hero"; the PCs should survive while lesser beings die around them. Is it realistic? No, but nothing else is in most RPGs, either, so that becomes a very weak argument.
Another argument for character death is that it's necessary for the heroes to fail. However, "death" and "failure" are two different issues which tend to get lumped together. A great example of these conditions being separated is the tabletop game Descent: Journeys In The Dark. In Descent, heroes who lose all their health are "defeated", which removes them from the game until they are revived. In this way, the Overlord can defeat the heroes in a quest without permanently killing them (and thus removing the character from the game). The heroes can all be defeated, the Overlord wins the quest... and the game goes on. The story continues! And the players do not have to "reset" and lose the investment in their characters. Again referring to the comic approach, the heroes suffer a setback (which happens, and makes for good stories), but the story continues.
There are numerous ways to avoid character death, ranging from their being left for dead, being taken captive, or even a miraculous escape. Again, comics are rife with examples of this. Instead of a character being slain outright (and his story ended), perhaps he is taken captive by the orcish tribes. Now the other characters can arrange a rescue attempt, the captive hero can try to escape, and so on. Again, the story continues!
Now, this is not to say that character death should never occur! It depends on a certain level of "good faith" between players. If a low-level PC runs off to a dragon lair or attacks a demon lord, they should probably be obliterated in short order. Also, there may arise a situation where a player feels their character should die, usually in a grand heroic sacrifice (holding off the enemy hordes so the others can escape, etc.). Sometimes it's as simple as a player just not liking their character or wanting a change of pace. In those situations, death may be good for the greater story and can be embraced for that.
Ultimately, I think the story as a whole should take a greater precedence than game mechanics. It's all about having a good time, and no one enjoys having the story for their favorite character brought to an abrupt end because of a night of crummy dice rolls! Instead, let them suffer defeat but live to continue their story and fight their way back to victory. Obviously, this approach is better for more story-driven players, but I think that it would add much more to the game than it might detract, and allows the stories of both players and characters to continue!