Right, to start with, most of the other answers are bobbins. I'll tell you the truth!
There are three HD channels available through Freeview at the moment, although not available in all areas yet. The channels are BBC HD, ITV HD and Channel 4 HD.
All programmes shown on BBC HD are in HD. ITV HD and C4 HD just show what is on the regular channel. If the programme was filmed in HD, it will be shown in HD. If not, they will upscale the programme. The upscaled programmes are noticably better.
For example, Countdown on C4 is not filmed in HD. However, Rachel Riley and her legs look much better on C4 HD than on regular C4 due to the upscaling.
The proper HD is much better of course.
On every other channel, there would be no real improvement despite the HD source/cable etc. Having said that, it depends how good the Freeview HD box is. There are cheaper ones (sub £100) and dearer ones - £150 and up.
As to whether it's worth it? It's down to you. Do you watch much TV? Is HD something you're desperate to have? If so, maybe it's worth it. It depends how easy £100+ is for you to come by.
Finally, I'll mention the aerial required. There is no such thing as a digital aerial. There is, however, the right type of aerial to receive digital. Many people already have such an aerial and don't need to replace it. As for the HD? If you can receive all the digital channels you're supposed to, you have the right aerial. The HD is just the same as any other channel - don't believe this 'HD aerial' nonsense. No such thing!
To check whether you might be able to receive HD, check using your postcode at: www.freeview.co.uk/availability