I, too, have always been too careful with water to experience any drastic flooding. I put doors everywhere so the one time I can recall having an unexpected flood it just filled up one room (and all I had to do was close the main inflow valve for a while to let it drain again).
Just recently, though, I deliberately had a bit of Fun with a huge mountaintop reservoir perched overtop of my fortress. I had it rigged to flush out my courtyard with vast amounts of high-pressure water, sufficient to build a temporary "water pyramid" over it and drown goblins without needing a roof over them, and decided to see how it would flow through my corridors and mine shafts if I set my airlock the wrong way when I opened it. Even with doors everywhere, maybe a third of my rooms wound up flooded. Dwarves would struggle desperately through the 7/7 halls in search of air, open the nearest unflooded room to take a gasping breath, and instantly doom everyone that had been cowering inside as the still-pressurized water swept through in a matter of seconds.
I "re-ran the simulation" (ie, save-scummed) three or four times and found that very different groups of dwarves would survive each flood even though they started out identical. I also found that having deep mineshafts to drain water into didn't help much in the short term since they were all very narrow - even pressurized the water didn't flow down them fast enough to make a short-term difference. In the long term it was different, since the reservoir had a limited amount of water in it the mines eventually drained the majority of my fort. The flooded rooms had to have their doors taken off their hinges once the water levels outside had gone below 7.
I _almost_ had my first tantrum spiral. Several dwarves did go mad. But the populace pulled back from the brink, darnit. It turned out that my dining hall was highly flood-resistant... though one time it did flood was a lot of fun for the dwarves that died in it, since the walls were riddled with windows looking out over open unflooded space.
I can certainly see the appeal of the challenge of recovering a fort like this. It'd be even better if once Toady puts in the limits on map-reveals we could have fortress reclaimation where you can't see everything at once - the excavation and draining operation will become much more of a delicate task if you don't know which doors have an ocean straining behind them.