Assuming that your source is a DVD player, S-Video is better than composite video (single RCA jack), and much better than using a RF modulator / antenna cable.
Component video (especially progressive component video) can produce a better picture than S-Video, if the player and the TV are both equipped to handle it.
If the video source has an S-Video output, then yes it should give a better picture. The S connection carries the color and black/white info separately and they are recombined only just before displaying them. Since each signal piece is not intefering with each other, you should see a bit of an improvement.
If you have the 1st Austin Powers DVD, run the scene where he is on the bed (in the plane.) His shirt has fine stripes on it and the difference between composite (the yellow connection) and S-Video will really show up. The rainbow-ish effect (called moire with an accent over the "e") that is on composite won't show on S.
Most DVD players have both Composite and SVideo outputs active. Hook up both, freeze a scene from a modern DVD (hopefully with lots of straight lines and bright colors) and toggle your TV back and forth between the different connections. The difference is usually visible.