The late 1990s to mid 2000s was the era where sound cards really mattered. Those were the days of hardware accelerated sound, when PC games actually used the sound card's processors and delivered superior effects that we no longer have today. Only Creative's X-Fi processor provided this, so ASUS sound cards were quite useless beyond the general sound quality improvements over onboard which Creative also brings. Furthermore, Creative did license their X-Fi processors and let other companies, such as Auzentech, use it.
Left: Creative Sound Blaster X-Fi Titanium HD, the highest quality X-Fi sound card ever made and easily on par with today's flagships. Right: Creative Sound Blaster X-Fi Titanium Fatal1ty Pro, the highest quality Creative 7.1 capable X-Fi sound card ever made.
The days of glorious superior hardware accelerated video game sound effects was a three part effort, requiring input from the game developers (to make their games use it), Microsoft's DirectSound3D audio API, and Creative's X-Fi processor. Nowadays, both DirectSound3D and X-Fi are dead. DirectSound3D had an open source successor called OpenAL, which is no longer entirely open source although OpenAL Soft is an open source software-only implementation that remains. But X-Fi processors are dead and modern sound card processors, including Creative's latest Sound Core3D (and of course all ASUS solutions) only process OpenAL instructions via software.
Long story short, for modern games sound cards are unnecessary. Want to improve sound quality in general with your speakers/headphones? Assuming the speakers/headphones aren't crap (which they probably are), get an external DAC (and amplifier for headphones) which is better.
Today's sound cards lack hardware accelerated sound support, but so do the games. Sound cards are lacking in very obvious ways too; Creative's sound cards only support up to 5.1 surround these days, while a few previous ones (like the above pictured X-Fi Titanium Fatal1ty Pro) support up to 7.1. We're not even speaking strictly of analog connections (hardly anyone uses analog surround with sound cards anyway), but DSP (processor) support. In Windows, with a modern Creative sound card you can't select more than 5.1.
Then there's the output connections offered by sound cards. No HDMI for starters. So for those of us using surround systems, we have to either use a surround system with active speakers connected to the sound card's analog outputs, using the sound card's DAC. Two major problems with this: There are no active center channel or multidirectional surround speakers, to my knowledge, so you'd be using bookshelf speakers all around (turn one sideways for the center channel I guess). The other more blatant problem is that only five sound cards have respectable enough sound quality: ASUS Xonar Essence ST, ASUS Xonar Essence STX, ASUS Xonar Essence STX II, Creative Sound Blaster X-Fi Titanium HD, Creative Sound Blaster ZxR. They're expensive and are still much worse than practically any A/V receiver.
Creative Sound Blaster ZxR, Creative's latest flagship sound card. Essentially the same as the Titanium HD but no actual hardware acceleration support, a headphone amplifier is included, and better shielding. Still uses a DSP with only 5.1 support. Still no HDMI output.
Ah, but an A/V receiver! How about passing a digital signal to that, bypassing the sound card's analog section and using the receiver's superior DAC as well as actual home theater passive speakers, including center channel and multidirectional surround channels? That's what almost every surround sound PC gamer uses, it's the best option. The problem here is the lack of HDMI output. HDMI can carry an uncompressed 7.1 sound signal. Since sound cards (except maybe one I think?) don't have HDMI, we instead have to use S/PDIF out (almost always via optical TOSLINK rather than coaxial, and on that note coaxial is of course slightly better...). S/PDIF can only carry a compressed 5.1 signal.
The compression with DTS Connect/DTS Interactive isn't horribly offensive. Games don't use many high quality/bandwidth sounds and many are computer generated sounds anyway. Still, better 7.1 support is nice as is uncompressed sound via HDMI especially for HTPCs. It's still possible to use uncompressed surround via HDMI using a video card or onboard graphics HDMI connector, but some may want to use various sound card features which onboard wouldn't provide, not to mention OpenAL/DirectSound3D/EAX games need a Creative X-Fi sound card to sound their best.
Sound cards can be of some use to audiophiles, at least if they had other digital outputs than just optical TOSLINK. They need to offer coaxial (RCA jack), HDMI 2.0, USB, and RJ45. It's better than connecting to a DAC or digital interface from onboard. Better SNR, and PCI-Express bus is less busy and cleaner than USB.
In conclusion, we need sound cards to once again have hardware acceleration support. Bring back X-Fi. Make OpenAL more appealing to game studios, make it open source again. Furthermore, 7.1 support needs to be standard, and so does the inclusion of HDMI output. Other digital outputs would be nice too for maximum connectivity, flexibility, and usefulness. But there is absolutely no sign of the sound card industry digging itself out of its grave and catching up with the times.