I vote blaze of glory. The odds of us seeing that ship, or anyone on it, ever again are almost nil. I say ship AND crew goes out in a blaze of glory that would make Worf cry with joy.
I'll second that possibility, and here comes the sleep-inducing diatribe as to why:.
The "E" movies were a bit hit or miss, and even in GEN - despite the saucer crash - could have had the "D" go out in a proper blaze of glory rather than "a blaze against the saucer's hull from frying in the atmosphere next to the freshly laid omelet ingredients that were flung out of the main shuttle bay when nobody was looking, and don't forget the windows were transparent aluminum and not glass so don't pretend that lame soap opera action shtick didn't start before the 21st century, tee-hee." As cool as the "D"'s crash was, the reasons for it require a ton of headcanon - and even then it doesn't feel like a reversal of "David vs Goliath" done with any tact or gravitas that was needed (and deserved). The crash on its own looks spectacular, but felt like a disjointed set piece.
Considering Han and Luke got killed off, and so was Kirk back in the day, the idea of letting heroes die in tragedy seems to be a trope's trope. (not to mention, Kirk's originally filmed death felt like it had more genuinely tragic meaning than the bridge he was on bopping down the hill, while he was still on it. Followed by nice overly-long soap opera speech afterward. Followed by a burial comprised of covering his body with stones (yeah, Red Letter Media honestly had a frickin' field day on Kirk's death's aftermath and they can't be blamed. Then again, the saucer AND corpse were brought back from Veridian III, so in retrospect maybe RLM, me, and others are wrong. I might be very happy with that come next week...))
Besides, not since "Blake's 7" has there been a massive downer of a series finale. Part of me thinks that wouldn't happen, but "Blake's 7" had its ship crashing in its series finale too. Aren't loose associations fun? I think they are.
If nothing else, maybe the crew survive but the ship - a character in its own right - is the one that is destroyed.
That, and the Borg are glowing green again. It's so much nicer (and more evocative) than the neon blue, which for all we know may have been due to another splinter off the Collective and one with additional fashion sense at that.