Each can look great if you know how to use them. There are only a handful of SD cameras that produce good quality footage, but plenty of HD cameras that perform well. Personally, I would rather see a video shot on a VX/MK rather than one of the many Sony Handycam/Opteka X Grip/Baby death setups that are so very common with scooter kids.
Newer HD cameras are generally easier to use, with HDD and Flash memory. But, your computer has to be pretty beefy to be able to edit good HD footage.
SD cameras use MiniDV tapes, which require you to playback the footage and record it into your system, which of course, is a real pain. But, SD footage is a lot smaller of a file and you don't need to be using a transformer to edit it.
There are the pros and cons to each side, it all comes down to knowing how to use your setup and personal preference.