I don't like the old school fantasy Vietnam style where characters keep dropping like flies, but I definitely want the death to be a possibility in D&D, and other similar combat heavy action adventure games. I want the character death to be rare, but possible. Oh, and I greatly dislike resurrection magic* and tend to delete it from games I run. Death should be a big deal, and actually stick. Having resurrection as possibility is a total drama killer.
* Revivify is an exception, as that's more like intensive CPR.