Which Shutter Speed
When looking to keep images sharp, a good general rule of thumb is thus - the maximum shutter speed you can use is 1/ your focal length. For example, take a focal length of 50mm - the longest shutter speed from which you can expect a sharp image is 1/50 second. Image stabilisation goes some way to extending this, but it's best not to rely on this if razor-sharp images are needed.
Should you be looking to produce panning shots with some action-based motion blur, somewhere around 1/15-1/30th will keep enough of the action sharp, yet provide discernible motion blur.
For long exposures, for example when capturing star trails or blurring moving water, a tripod will be required. Anything below 1/30th second won't retain absolute sharpness and much longer exposure times will be so great that handholding is impossible. Shooting in considerable darkness can take some practice too, as it's tricky to meter for available light. Trial and error goes a long way.
Then, of course, there's getting the overall exposure correct. It's not always possible to use a fast shutter speed due to low-light conditions, as much as a long exposure may become overexposed when there's too much light. A balance against available aperture can assist in partially accommodating for this, but this adds the consideration of depth of field control too.
1/4000th sec - Freezing motion
If you're shooting a particularly fast-moving subject, then a faster shutter speed will freeze the subject mid-action. 1/4000 is common for DSLRs, with 1/8000sec speeds available on top-end kit.
1/250th sec - Handheld sharpness
If sharpness is the name of the game then the faster the better, assuming a desireable aperture is being used. In good light and at regular focal lengths, around 1/250sec is a good marker.
1/30th sec - Panning
The art of panning combines a steady hand with an exposure just the right side of sharp. If the subject is steadily panned it will appear relatively sharp, while the motion-blur of the surroundings will add to the scene's dynamic.