Many replies here already, but I'll add my own observations
While I have the possibility to just use RGB SCART cables here, I've also got the s-video adapter from AmigaManiac. I've made test video captures from all three (composite, s-video, RGB):
By far the larger difference is going from composite to s-video; as the luma and chroma signals are separated, you get a greyscale picture nearly as good as with RGB, but colours of similar brightness still get blurred (in technical terms the chroma bandwidth is small). With RGB of course the image is perfect.
If you can get a CRT TV with s-video input for a good price, it would in my opinion do absolutely fine for games and still be decent for at least Workbench usage. And you could also use the CD32 with it.