The Prophecy demo is unique in that it's a little story that goes alongside the game. The WC3, WC4 and WCP 3dfx Test demos are all new missions but they don't have any storytelling associated with them.
The WC3 demo is a patrol where you fight off several Dralthi. It's interesting because the game has a slightly different 'feel' to it in places.
The WC4 demo is a timed gauntlet where you launch from the Intrepid and fight off wave after wave of pirates. It has some cool stuff in it, including a weird easter egg where you get a stormfire as a powerup! (Lock onto Decker's shuttle which is flying around and follow it for 30 seconds... it "drops" a stormfire you can collect).
The WCP 3dfx demo is a gauntlet against the Kilrathi with some surprises if you get far enough.
The Privateer 2 demo isn't a unique mission, it's just a timed session where you can fly around the tri-system (IIRC you have one SOS mission already assigned to you, but you can't really get there and finish it in time.)