Picture controls are basically in-camera processing. So like Nick said if you shoot RAW and use processing software other than Nikon then only the image preview in the LCD will show the effects. If you shoot RAW+jpeg then the jpeg will be processed according to the picture control settings. If you shoot jpeg only then you're pretty much stuck with the results.
If you do shoot RAW one thing to be aware of is that the image that you see in the camera LCD during preview is a jpeg which has been processed according to the picture control settings. So if you use the histogram and/or "blinkies" they indicate the exposure/dynamic range of the jpeg which can be significantly different than that of the RAW image.
Dan's answer is way more complete than my terse note (I'm on my first cup of coffee, and Dan, in Alaska, ought to be asleep)
Many of us use the neutral or standard picture control since it least affects the preview image and makes for a more accurate histogram and blinkie display on the camera LCD.
Another excellent image Will. You are certainly the master of jpg. However, the vast majority of photographers would benefit from shooting raw f they want to get the best results - as long as they want to spend time at the computer.