I'm talking about the red, white and yellow cables.
Depends on the TV model. I run various video connections including Composite PlugnPlay portable games on my HD CRT Wega. All look just fine including RF, Composite, SVideo, Component and HDMI converted to DVI. The HD CRT has the flexibility of supporting newer game consoles and older non HD signals with no upscale issues. That said utilize the best cable connection for each game console and player's capability, SVideo is better then composite for example.
From personal experience having wired up hundreds of HD and SD CRT's, i can assure you a SD CRT will look better than a HD CRT in composite and way better in S-Video. Of course this depends on the SD CRT.... a good old 1993 Trinitron will amaze you in S-Video while a old 1995 Toshiba may disappoint you.. Like CRTGAMER said... the big advantage of a HD CRT comes from its Component and even DVI/HDMI capabilities.
Personally i'd choose a SD CRT or a high-end multisync monitor well before i'd buy a HD CRT. The only good HD CRT in my opinion are Sony's DA4 Chassis using CRT's. Apart from that one... Samsung's LG's Toshiba's and all other brands introduce too much digital noise and since almost all HD CRT's are not multisync but locked in 1080i.... its not worth it.
If you do own a SD CRT. Plug it in S-Video like CRTGAMER said. Its MUCH better.
And some, like my Panasonic Tau HD, upscale the SD. I get a smoother pic but at the sacrifice of losing detail (my old '93 GE shows more detail). I can't turn it off either but in general it makes SD look better. Not being a gamer I don't know if this would be a plus or minus.