Oblivion: A Brush With Death.  You go through a painting into a version of Oblivion rendered as brush strokes.  It looked amazing and it's always nice reuniting a couple.

Link's Awakening:  Late in the game, you'll discover that a ghost is following you.  If you confront him, he asks you to take him home.  You do, and discover that his home has fallen into disrepair.  He realizes that his loved ones are gone and asks you to take him to his grave.  Incredibly moving for just some 16x16 sprites and simple text.

World of Warcraft: The Day that Deathwing Came.  This one bright spot in the Cataclysm expansion had you listening to three drunk guys telling tall tales about growing to giant size and punching Deathwing in the face.

I'm sure I'll think of more later.

Oh man, to be honest, I have tons. Being asked to recall them specifically, though, makes it hard to sort through them in my head and pick only a couple.

We'll start with Final Fantasy Tactics. There were a lot of little optional side-quests and optional characters that you could have easily avoided entirely and still beaten the game, but I feel like a lot of depth and joy is lost by skipping them. They don't advance the main plot at all - if you take them out, it doesn't affect a thing - but having certain characters in my end-game party (or even just occasionally switching it up in random encounters with them) added an element of fun.

I think the fun was in having more choices, and in the unique abilities and stories many of these characters have. It made you WANT to take the time to make sure you could get them on your team. The stories weren't all that deep, but they were awesome tidbits in a world with an already rich story and environment.

The Mass Effect games are another example of questlines that are impactful. You can zip through the game without worrying too much about your crew's approval or relations to you, but taking the time to do each of their individual questlines just made the game that much more compelling for me. All of the stories were specifically crafted to allow you to gain insight into who each character is, what their goals and wants are, what their past was, etc, and that made the urgency of completing the mission and beating the game more real for me.

In general, as I'm sure you've by now guessed, for me personally it's all about the story. I guess that's the creative writer in me. I will go the extra mile to spend time on something if it's going to enhance the lore of whatever game I'm immersed in, if it's going to give me another character to care about or a reason to empathize (or hate!) a character I already have.

I think lots of other people who aren't interested in story probably like quests that give them something aesthetic, or something that will challenge them (like a cutscene that's hidden behind a forest filled with REALLY dangerous enemies in an old game called Lunar). For me, though, if it doesn't somehow enhance the story, I'm not usually inclined to put in the extra effort.

Mass Effect 2 Quarian and Geth storylines especially when they merge.  Two interesting factions you deal with, and based on how you deal with them you get different outcomes at the end (which may have been in #3?  I forget).  Real (for those involved, not the story overall) outcomes based on your actions.

Baldurs Gate 2: The mage tower and fighter keep questlines.  Building a persistent base was pretty unique back then.  It was then improved upon in NWN2 where you gain command of a keep and have a number of quests involved to improve it, and so on.  I really loved this - when RPG gains RTS and/or management aspects, my ideal game would be along these lines.

Planescape Torment... basically the whole thing?  But specifically where you deal with the criminal element in sigil and go underground with the smart rats, and also the Ravel? "what can change the nature of a man" extended story.  Unraveling the mystery of Dak'kon too and the Githzerai story stuff.  So many more memorable bits, it's really the #1 RPG for me still.

The Shale storyline in Dragon Age 1.  Discovering the lore about her, the golems, and the Dwarves was pretty awesome.

Sadly I can't remember much from earlier RPGs like Ultima and DND gold box games so no particular quests stand out.

For MMOs it's more about zones than individual quests, when the zones have overarching questlines/feels.  The revamped Undead Hillsbrad zone was solid with good comedy and story elements.  Lord of the Rings Online Moria nailed the feeling of being in an epic underground dwarven empire that fell to ruin.

Homeworld Mission 3 the burning of Kharak ("the subject (a captured enemy that destroyed their planet) did not survive interrogation" said in that classic Homeworld understated professional tone) and the Homeworld Kadeshi (they were left behind and became scavengers and that eventually became their religion, in contrast to the Kharak main character race's pro-science and technology views) Nebula missions.  Those were added in last minute by the people who went on to make Homeworld Cataclysm (now called Homeworld Emergence) and went on to become Kerberos Productions.  Homeworld has the best plot of any RTS due to those missions and the "journey home" trope being the third reason it is so good.

Also basically all of The Witcher 3, but that was a special mix of books and books of premade lore by Andredj Sapowski, a good plot structure of being the best hard boiled detective game except for maybe LA Noire, and ripping off lots of little known folklore and lots of original writing so every side quest was unique and NOT a boring fetch quest, not even the "can you find my cooking pan" joke fetch quest.  The entire Hearts of Stone expansion is the best, especially the spoon wight, the main antagonist with the heart of stone and his three impossible tasks, and the Eldritch horror Master Mirror that gave that man his heart of stone who drives you blind and exposed to evil forces if you read too much about Master Mirror, then thanks you for your interest in him and does you a favor and draws you a circle of protection against those evil forces that you will die if you ever leave it.  The monster slaying contracts in The Witcher are in my opinion better than Monster Hunter.

Lineage II: Seven signs quest series. They were long and at times very annoying, but interesting in their own way.

I LOVE this questline! Another awesome example of how something that isn't necessary to the overall main story can still be compelling and interesting. It doesn't have to give you a shiny new item or superpower to be worth it - the story, often times, is its own reward.

I beg to differ.

I read it all (short stories and saga) several times, more or less when it was originally published, long before anyone even thought about video game based in this universe, and I played games (W1, W2, W3) decades later - trust me, I know exactly what I am saying and what I really love here - devs in W3 created something more than just gaming version of the books and it has value on it's own terms. With all due respect to the original author of the universum, in my opinion as a reader&gamer some angles of stories in game are actually better than their inspiration. There are a few reasons of that, first being original default target audience of books - they were mainly popular among older teens/early 20., while game was targeted to people already several years older, like original fans of the books decade older - thus game touches slightly more mature content in broader spectrum, using more interesting narrative. Another thing is ability to translate the idea (world/story/characters) to absolutely different medium, what is art on it's own.

Could be matter of opinion of course, but I've seen my fair share of games "based on" something to see the difference here. In context we are discussing here - quests - actual books had close to no influence on them, besides inspiration in very, very broad sense. Earlier Witcher games based on books more, W3 went farther - and good riddance.

Either way, there's some pretty amazing quests in the Witcher series.

Actually there's a quest in Ultima VII where you have to find a wisp and communicate with one - it's really quite unique and interesting how it develops. I think for me those types of quests are really my favourite ones. The ones that aren't necessarily unique because of how they work mechanically, but unique because of who's involved in them.

I think I like these quests so much because you get an insight into an element of the world that the writers/developers don't often explore. And I think that sort of insight can lend a certain depth to a setting that it wouldn't otherwise have. By giving us insight into how the trolls of the Witcher setting think, behave, what their motivations are, what they care about - yeah they're funny, and interesting - but they're also showing us another view of that world and a set of interactions that tell us a lot about it.

I liked the quests of Grods in Space, I needed cheat sheets though. it was a clever game. 

Eternal Dagger, Wizard's Crown:  You could use your experience points to improve your character the way you wanted.  Spending Exp. points you gained fighting with a war hammer to upgrade skills in another weapon wasn't realistic though but Dojos that could do that to a high level would have solved that.

Legacy of the Ancients:  It was a bit easy to get lost in the dungeons, but I liked that you could win money playing black jack and stash some away in a bank to start compounding.  That was something often missing in D&D Games.

Wrath of Denethenor was really unique.

One game I really enjoyed was Sid Meyer's Pirates.   I played it both on the Amiga and the Commodore 64.   There were some quests in it.

Ultima I and II I really liked.   In Ultima III they started to try to be fancier and the game wasn't as fun as a result.