Demon's Souls is a game without many set piece moments. There are a few, but unlike some games where proceeding through the set piece in the exact manner you're suppose to is the only way forward, Demon's Souls allows you to screw it up and miss out. You can still progress, mind, if you are determined enough/have enough arrows or magic, because most of these moments occur during boss battles. It's just that you'll miss out on what may well be some of the best moments in the game. Considering how fantastic some of these bosses are... Well, allow me to elaborate on one of my personal favorites.
You are a demon slayer, come to the kingdom of Boletaria for reasons entirely your own. Having braved the colorless fog encapsulating the land, you are soon killed by a single blow from an enormous demon. However, you are saved from your ignominious fate by the actions of the mysterious Maiden in Black, who has bound your soul to the Nexus at the center of Boletaria. You soon meet the last Monumental, a guardian of reality, who presses you to slay every last demon in order to draw yourself to their source, the Old One, and lull it back to slumber. Unable to die, and at risk of fading away to nothingness, you embark into the various areas infested by the demonic scourge in order to gather the souls of man and demon, growing ever stronger with their collective power.
Thus, you slash/smash/stab your way through hordes of slavering madmen, hideous demons, and even a dragon or two. Eventually you find yourself in the Shrine of Storms. Here the demons possess the skeletal remnants of the former inhabitants, the Shadowmen, summon the spirits of the dead, and assume the form of their ancient pagan deities. At the top of the Shrine, the dead are judged by the gluttonous Adjudicator, commanded by the golden crow perched on his crown to slice up and devour the unworthy and those who ate birds in their diet (which is kind of funny in a petty sort of way).
Nearer the bottom of the Shrine, the body of the Old Hero is partially entombed above the exit, where as a punishment for some long forgotten transgression, he was made to overlook the chamber where the worthy dead would be purified in the rain. The presence of souls awakens his spirit, blind as he was in life but just as powerful.
Beyond the chamber, the final stage of the Shadowmen death ritual involves laying the purified body out to be eaten by the Storm King... So guess who shows up?
Your eyes adjust to the gloomy light outside. An unearthly cry spreads through the air before you see its source emerging from behind the clouds. Slowly flapping through the sky, its long, serpentine tail trailing behind it, comes the Storm King. Lesser storm beasts in the King's likeness roost on its stomach and back, detaching and assaulting you with crystallized lightning as the King observes with a solitary eye, far out of your reach.
Now here's where you can miss out. It's very likely you will attack and kill the storm beasts and the Storm King with ranged weapons or magic, and you might not even head into the field of standing stones until after the fight, whereupon you would discover the most unique weapon in the game. If, however, you decide to dash madly towards the stones for cover, you may well notice something jutting out of the ground as you hide. A pair of lizards scamper away at your approach, and you draw a curious sword out from the ground at the base of a great, slanted slab. By necessity, you would again take cover from the storm beasts' attacks. Curious about what you found, and secure behind the rune covered stone, you quickly look at your new weapon.
It has solid attack power... And unbelievably low durability. What use could it be?
You check the description.
A legendary large sword with a thorny blade, named for one who calms storms. It is said the ancestors of the Shadowmen rended the storms and clouds in the sky with it.
Now abandoned, with much of its power lost, it is an average large sword. However, if it is used in the monolith forest where ancestral spirits slumber, you may be able to reawaken its ancient power to rend the sky.
With nothing to lose you equip the weapon, and test its moveset with a swing.
Your character holds the blade aloft as wind rushes around it, before coming down with a swipe that unleashes an enormous slash of razor sharp air. Now the playing field has become a bit more even. The storm beasts are felled quickly, being ripped apart by the force of the blade. When the last beast falls, the King dives lower, releasing a barrage of enormous scales before flying away to make another pass.
Dodging the flurry of scales and countering with Storm Ruler, you eventually bring down the Storm King, who dies after giving one final, unnatural cry.
Its soul is yours.
Demon's Souls doesn't really give you power all at once. It's really more of a slow drip feed, and when you look at where you are after playing awhile you can see that your growth has been considerable. Spells and miracles aren't massive cannons of force unless you build your character to make them that way, and even the more powerful spells are often limited after a fashion. At the bottom of the Shrine of Storms, you are capable of wielding this sudden and immense power, which is possibly unparalleled with anything that you may have used until or even after that point. It isn't mandatory that you do use it, and it's even probable that you miss it entirely, and you can't use it anywhere else. It's a great and refreshing moment that doesn't break the mold of the game.
This may be over-thinking it a little, but it could be considered almost symbolic of one of the strongest tenets the Souls series possesses: Options.
You can use this thing, this may be what the designers intended you to do in this situation, but if you don't pay attention you'll be completely oblivious to it. You need to explore to find the best things, even when danger is bearing down on you, but even then, if you don't follow the intended path, you can overcome the challenges presented to you on your own merit, in the way you choose.
Or maybe it could just be there for players who don't have many ranged attack options.
In any case, using an ancient, spiral-bladed sword to summon whirlwinds that tear apart a demonic, cyclopean (in two senses of the word), flying manta ray manifestation of a dead culture's god, while the sea crashes below and crepuscular rays shine through the cracks in the clouds as they churn majestically in the sky is pretty rad.