Well... It depends.
I know my Scientific Atlanta box (Cox cable) will convert programming and put it out on S-Video if that is what I want to use in my setup. HD programming will look much better than your analog channels because you are starting with a really good source. But, because it is HD, you will have gray bars above and below your image (widescreen).
It's kind of like watching a DVD on your TV. The DVD should look really good, even though your TV is older. The HD material will look good, because the original is so good to begin with.
Just double check with your cable company to make sure that there is no problem with that change... They probably won't have a clue, so make your upgrade contingent on it actually working on your system.