I'm thinking of buying Digital Juice's stock video set, but it's only SD. If I use it in an HD production, won't it look terrible, even if it's not shown full screen? Is there any way to make it look like HD?

It won't look terrible if you are delivering or viewing the project in standard definition, but there will obviously be a clear loss of resolution if you try to scale 720x480 up to an HD timeline, whether it's 720 or 1080.

Otherwise I'm not sure what you mean by making SD "look like HD". If it's not full-screen but rather picture-in-picture, kept close to it's original size, that is one way you can avoid the pixelated look. It doesn't have to look terrible if you are careful in how you use it.

There are upconverters, too, that work reasonably well. Cineform's HDLink will upconvert to HD, and to me it looks good. Of course, I don't sit there with a magnifying glass going over each frame. But in playback, it looks fine to me.

Thanks,
Matt