Depends on how much you wanna spend. Mid range and budget then Realteks good enough or something like a Sound Blaster Audigy. If your going high end then it all depends on your budget but a decent 5.1 EAX 5 system will blow most stuff away especially when gaming.
Between sound devices housed in the computer... Naaa, you won't hear much difference. In fact they're just about all "better than CD" sound nowaday.
However, it does depend on what you are doing...
For example if you are dealing in audiophile grade playback with 24 or 32 bit sound sources (eg. FLAC files) you may want to consider a mid to high quality stereo DAC. These little gems connect by USB or Firewire and can deliver sound of truly breathtaking quality.
As you move up in feature/price levels for motherboards, you move up in quality for on-board sound. The better motherboards have excellent sound on board and many are designed specifically for integration into home theater applications. You have to remember, in most cases, you are simply moving digital data around just as you would a file - and that is not too hard to do.
The reality is the biggest variable in quality audio reproduction is always the speakers. That's because they are "electro-mechanical" devices. PC speakers are typically amplified - meaning the preamplifiers, converters (if any) and amplifiers are built into the speaker system, typically the sub-woofer. This is all part of the cost of a PC speaker system.
For a real reality check - quality home theater/stereo speakers easily cost many $100s or even $1000s for just one speaker! Plus it costs $100s or $1000s more for the amplifier/receiver electronics. So don't expect audiophile quality sound from a $200 or even $500 set of amplified PC speakers. Yeah, if you're playing a game or watching a DVD, the audio takes on a secondary position in the hierarchy of your attention, but if your goal is serious music listening, you will have to output your audio to quality external electronics (HT receiver or separates) and serious speakers.
Remember, loud does NOT equal quality.
And yes, even the most basic motherboard will have front panel headers to move front left and right outputs and microphone input jacks to the case's front panel for headphone use.
Thanks everyone on their input, much appreciated. Hey Dig, I was meaning on the addon sound card it self, if it had headers for the front panel ports on case. Or how does that work? If not and front panel is still plugged into mobo wouldn't that not allow me to use my headphones w/mic.
Oops! Yeah, I see you did say that. You will have to look at the specific card but I would be surprised if you found a card that does not support front panel connections. If you look, for example, at this card's specs, you will see under Connectivity, something similar to this,
Intel HD Audio Compatible Front Panel Header (2x5pin)
Going from Creative Audigy 2 to Realtek HD, I hardly noticed any difference at all. Creative will offer you a lot of add-ons like wave studio etc., which I personally was very proud of but never used. As for Realtek, it also has equalizer, environments, lots of settings - all nicely arranged in one control window.