I think the advantage of CF cards over SD cards is that they’re pin-for-pin the same as IDE drives so the conversion is relatively straightforward, it’s just that CF cards are more expensive than SD cards.
I feel like the general consensus (for both CF and SD cards) is that they’re fine for DOS and Win 9x, but for XP you’ll get performance issues and are better off going for an SSD.
I’ve personally had great success using a CF to IDE adapter for both DOS and Win95, and a Sata2IDE adapter for an SSD in windows 98 (plus Sata SSD connected to SATA port but running in legacy mode on a newer mobo).