What Joseph said is spot-on. The only time I've ever increased the sharpness setting other than to experiment with it is when I had to deliver JPEGs straight out of the camera and was using a lens I knew to be a little soft.
> what would be the optimum sharpness value between 0-9 and why ?
It depends. (I am told that I say that so much that it has become my middle name.)
On what does it depend? Well, it might depend on your post processing software. If that is CX2, the resulting sharpness will be applied even to your NEF (raw) files. With almost any other raw converter, the camera setting doesn't apply to the NEF file imported into Llightroom, Aperture, Photoshop, Capture One, DxO, etc.
It depends on your taste for sharpness. Some people are sharpness freaks. Some of us, not so much; for them, moderation is enough.
It probably depends on your subjects. If you shoot a lot of very detailed scenes - birds in dense winter brush come to mind - you are likely to find that 9 is far, far too much. Similarly, the other extreme might be dreamy, creamy, bokeh-heavy images of flower petals, or foggy high-key scenes. On such images, it won't matter what you use - there are no edges to emphasize. (That's what sharpening does.)
It could depend on your lenses. Some are so sharp that their resulting images don't tolerate a tremendous amount of sharpening, whether it's applied in camera to the jpeg, or subsequently in post processing. Other lenses nearly demand a heavy dose of sharpening. I have some of each of these categories in my arsenal. Of course, the subjects and lenses interact, and your taste governs what you want out of the images.
Note that the setting on a D7100 may be quite different than on, say, a D3x, despite the fact that they're both 24mp cameras. The D7100 has no antialiasing filter, the D3x does. That's in addition to any differences that are configured into the camera by Nikon.
What do I do? I post process with Lightroom and I shoot everything in raw, so the in-camera setting doesn't really affect the files that I use to produce my images. So I set a moderately high sharpness, so that the image on the LCD appears fairly sharp. That way I have an easier time judging in the field whether or not I got a crisp, camera-shake-free shot. The image that is on the LCD is buried into the raw file, but Lightroom essentially makes it invisible once the file is imported, so that's the full extent of the use of that setting in MY workflow. Yours might be different.
_____ Brian... a bicoastal Nikonian and Team Member
My gallery is online. Comments and critique welcomed any time!
Joseph nailed the majority of it, but I'd also add that the subject will sometimes define the amount of sharpness you want. Portraits of older people, for example, are more pleasing if they are a little softer. Showing every tiny facial wrinkle is not very flattering.
"Red is gray and yellow white, but we decide which is right ....and which is an illusion"
Image size is also a factor. If I am shooting a low resolution image for the web I will apply a lot more sharpening to retain visible detail in the picture.
You can test this for yourself by taking a RAW file and opening it and then saving it as a 2000 pixel image along the long dimension and then at 1000 pixels, 500 pixels, and 300 pixels. Open the files and compare the visible details and how much sharpening is needed to easily see them. As the size gets progressively smaller you will be able to see that more sharpening is required.
This is why in the workflow process sharpening is usually done as the very last step and after any image resizing has been done.