There are two kinds of sharpening.
One has to be done by the camera (or your RAW converter) and that's capture sharpening. It removes the softness that anti-aliasing filters introduce into the image. Here you're mostly concerned about microcontrast, or fine details. This is also the kind of sharpness you tend to see in a lens, by the way - the ability to render subtle details.
The second is creative or destination sharpening, when you use USM
or Smart Sharpen in Photoshop. This renders acutance, or edge sharpening. You perform this on either your camera's JPEGs or post-converted RAWs.
I just moved up to CS3 from PS7 and the difference in the sharpening tools (especially smart sharpen) is huge. It's like getting a whole new lens.