"Come on", I hear you say, "There is no such thing as nice noise! Noise is bad and we don't want it!"
And you are right. Noise is bad, and we don't want it. But sometimes noise is inevitable, and something we have to learn to deal with. Sometimes it might even be something you want - to create a special mood. And in that case it really helps if the noise is nice.
I have seen nice noise before - when introduced by myself to obtain a certain effect. Plugins like Alien Skin Exposure and Nik Software's Silver Efex Pro can produce some beautiful and controlled noise to an image, adding an ambiance or feel, which wasn't impossible without noise or grain. But the noise I have seen in files directly out of digital cameras has been terrible. Distracting blobs of color turning black areas into a puddle of brown mud with M&M's in it, and changing white areas to a grainy porridge of off-white rice. Not nice noise! Not at all.
But this weekend I have been shooting with a D3, and for the first time I can say that I have seen nice noise directly out of a camera. At 200, 400 and even 800 ISO it takes a very keen eye and some manipulation to make any noise visible. At 1200 and 2500 the noise can be seen at 100% resolution on a screen, but when printed on my HP B8850 even in A3, the noise almost disappears and a 2500 ISO image might as well have been taken at 400 ISO.
I must say that I am really impressed with the high-ISO performance of the D3. I knew it had something going for it - I did read all the reviews and reports like you did - but I'm still surprised that it was so good.
The picture of the car you see in the beginning of this post was shot at 2500 ISO and the Cooper above was shot at 800 - both with a D3. The angler shown after the post is there to show what noise can look like when it's really bad. That image was shot a few years ago at 3200 using a Minolta Dynax 7D. I'm sure my D200 would not do quite as bad in that range, but it wouldn't do well. You can see 100% crops by clicking at the samples shown right here.
I have always shunned high-ISO shooting, and staid well below 800 ISO, preferably at 400 and 99% of the time at 200 or whatever the lowest ISO of my current camera has been. A few experiments with really high ISO's have been fun but not very useful. My old Minolta 7D could shoot at 3200 and higher, but the noise was severe and in no way nice. My current D200 fares OK up to 400, degrades significantly above 800 and is useless at 1600 and up. I always shoot RAW and any noise reduction I do, I do in post processing. I won't leave that to the camera.
But when a D3 some day drops from the sky and lands on my lap, I will happily shoot at 2500 ISO and higher. I did so routinely this weekend, and all the high-ISO images are nice and clean. And as I said: a bit of post and a print, and the noise is nice rather than disturbing.