Using the RealTek as a baseline, yes, everything that's out there sounds better. Sound tends to "skip" when your CPU is under high load, something that doesn't seem to happen with PCI based sound cards or soundstorm.
When does your average PC user, even your average gamer, max out their CPU on a 2GHz machine?
The Live and first-gen Audigy sound "pretty good". There isn't particularly a difference between them. My main knock against those card at present is the fact that they need add-on boards for digital I/O support. ACL650s may or may not support digital I/O, but at least I don't have to buy a "Platinum" version of a card to get those connectors. Another knock for the Live/Audigy is that there's no way to force multichannel sound. Most sounds in Windows are stereo. In some circumstances (games, basically), they can support 5.1 sound. There's no way to set things to 5.1 and leave them there. At least, there's nothing built into the card that will do that.
Audigy 2 is a bit of an improvement. It DOES sound better. It handles DVD Audio with included software, which is neat for me and the other six people in the world with a collection of DVD Audio discs. It has some builtin digital I/O. Not perfect, but better.
The Philips Acoustic Edge can upsample to 5.1. It doesn't sound quite as good as a Live, generally, and I understand there are some goofy bugs with that card's drivers. Also, it's lacking digital I/O. Still, always-on 5.1 is not a bad thing.
I'm not particularly a fan of ESS-based stuff like the TBSC or Hercules Theater cards. They're pretty vanilla IMO. Step up from Realtek. Sound on par with the Acoustic Edge. Some support for digital I/O. I don't think those cards do quite enough to justify being add-in boards for a computer with integrated sound.
SoundStorm (nforce2, at least) sounds good. Like the Audigy 2. Its internal upscaling of stereo sound to Dolby Digital 5.1 seems to work better than Pro Logic II (the technology built into newer amplifiers to do the same thing), and the sound quality is generally there. Knocks against it are that most nforce2 boards don't have much support for digital I/O (I'm sick of saying that), and every once in awhile, there are "glitches" in the sound that's sent to one's speakers, which on my setup sound like someone blowing air over a microphone. I turn off my receiver for a few minutes and all goes back to normal.
My listening is normally done with sound hardware connected digitally to a home theater receiver. That's a very different perspective than an office user with a 2.0 or 2.1 speaker set, or a gamer with analog 5.1s.