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 processor 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 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 (was standard issue back then just like how DirectX graphics API is and was), 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. As for DirectSound3D, since it does not exist in Windows Vista and later Windows operating systems, a program must be used to convert DirectSound3D to OpenAL so that sound cards can process the game's DirectSound3D instructions. The only program that does that is Creative ALchemy, which is proprietary and only works with Creative sound cards.
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 on the market, 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 DAC quality for this use: 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 via encoders like DTS Connect and Dolby Digital.
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. They can be useful audio interfaces if they have a robust input section as well; Creative's Titanium HD and ZxR have the ADC quality (analog to digital converter) but again lack the connectivity to be particularly useful as digital interfaces for many people.
In conclusion, we need games and sound cards to once again have hardware acceleration support. Bring back X-Fi, rename it if you want. Make OpenAL more appealing to game studios, don't let these studios be attracted to Wwise, XAudio2, FMOD, or anything else. Furthermore, 7.1 support needs to be standard, and so does the inclusion of HDMI output. Other digital outputs and inputs would be nice too for maximum connectivity, flexibility, and usefulness, perhaps through a daughterboard card. But there is absolutely no sign of the sound card industry digging itself out of its grave and catching up with the times.