Shuck's mention of the combat shotgun in Doom 2 made me think about this..
My 3 fave weapons in a game:
Doom 2 / Double-Barrelled shotgun - The sound it made was so meaty! And if you ran up to a monster and fired it they'd explode!
Rise of the Triad / Firewall thing - Shoot it at the floor and this wall of fire flies out, incinerating your enemies, leaving them as charred skeletons, which made a comedy xylophone sound when they collapsed!
Unreal Tournament / Sniper Rifle - Perfect for HEADSHOT!s. And pretty useful for using as a normal rifle too.