Look at any review of the latest digital camera and you'll see at least a page or two of 100% crops of noise at different ISO speeds, and endless comparisons with 23 other cameras. At this point I think it's fair to say that the engineers have successfully slayed the noise dragon. Both Canon and Nikon have full-frame cameras that are comfortable at 6400 and higher (notice I say "comfortable", that's actually usable and not just in special circumstances.)
One side note I'd like to make about those noise comparisons before I go on. They're not at all real world. Usually they'll shoot the same scene on a tripod with the same lighting. All very scientific and objective, but most people are not sports shooters who need fast shutter speeds and so up their ISO when they've got decent light. Most people are like me and up the ISO when they don't have enough light and thus high-iso noise is accentuated buy dark tones and shadow. That's why I said 6400 is comfortable. Ok, done with my mini-rant, now on with the show.
My old 5D was for a long time the leader in the noise race, then the Nikon 12MP cameras came out and upped the ante, then the 5DII came out with similar noise but twice the dots, etc. The thing is, the noise we're dealing with now is leaps and bounds better than on film at the equivalent speeds. Last year I was walking around with my sister carrying my Leica filled with a roll of Portra 800 and when I got home and scanned it, I was very surprised how much grain there was. Here's an example to the right, and that's a 50% crop (here's a link to the whole image in a post from last year). I'd say it's the equivalent of at least 3200 or even 6400 on my current digital. That's at least a two to three stop advantage.
Yes that's 35mm, and medium format and large format are better when it comes to grain. I've shot the Ilford 3200 speed film on my Hasselblad when traveling and loved it. The grain however was definitely there. Medium format film compared to 35mm digital, I'd give the edge to digital. Don't even try to talk about 4x5, what was the last time anyone shot anything over 400 speed film. I'll agree large format is amazing, but it couldn't be further from 35mm digital in workflow or convenience.
You could also argue the differences between digital noise and film grain. Sure, I'll agree that as a general rule of thumb I'd rather have film grain. But noise has been getting better looking, and as resolution goes up, it gets smaller relative to the pixels. Which is something that people who compare the relative noise of the D3 with the 5DII rarely mention.
Here's the kicker though: Lately I've been adding grain to my images, especially ones shot at 100-400 ISO. That's right, I'll open the image up in 32bit Photoshop (yuck!) and create a layer of medium gray and run the Alien Skin Exposure plug-in to add film grain to it (I choose the 120 size grain). Then I change the blending mode to overlay and opacity to taste. Now, you might ask, "Why in God's name would you want to ADD fake film grain to a clean digital image!?". Well to answer that, I'm going to have to take you on a quick little ride down my memory lane.
I went to school for music and not visual arts and did a lot of production work where I soaked up just about everything I could get my hands on about digital audio. I could write for days about how different aspects of the digital/analog battle in audio correlates to the digital/film battle in photography, but for the moment we'll keep it to one facet, and that's dither.
The process of analog to digital conversion in audio is much like a A/D converter in a camera. Most importantly in that the louder or brighter the signal, the more information that is used to capture it. So in audio that means that really quiet things down near the noise floor tend to flirt between being on or being off. For example, if the scale of loudness, for the sake of our conversation, goes from 1-100 (100 being clipping) then there will be some really really quiet sounds (or overtones and harmonics within other sounds) that sometimes register a 1 and sometimes register a 0. Basically coming in and out of existence as far as the recording goes. This shows itself as all kinds of low level distortions and some people say it's audible, blah blah blah.
The point is that recording people decided a long time ago that if you added really quiet noise to the signal, those quiet sounds wouldn't go from on to off, but would rather go from audible to being lost in this very quiet noise floor which sounds much like hiss on an analog tape (remember that stuff?) The crazy thing is that listening tests showed that adding this noise, or dither, actually made the recordings sound better, even though technically, you were making them less perfect.
So, how does this effect photography. Well I guess is does in two ways. The 1 to 1 corresponding effect would be to add grain to a digital images which has a lot of dark tones which have "blocked up". That is, that there aren't enough numbers in the data to describe enough levels in the darkest stop of the image right next to black. By adding grain, you'll make the transitions between those levels less noticeable because the differences will get lost in the randomness of the noise pattern instead of being an obvious line between black and one level above black. You might be losing ultimate image quality, but you'll end up with a more visually appealing photograph to the viewer.
But after all that explanation, that's not how I've been using the film grain lately. Mostly I'm using it to hide my mistakes, primarily in skin. Using the clone tool and healing brushes most of us can handle a few blemishes and wrinkles. But if the need for cloning is extensive or you've got to clone out a big chunk of hair from in front of someone's face, it's not as easy to make it look natural and blend with the skin around it in a believable way. Film grain to the rescue. By adding the grain you're bringing back some of the texture that too much 25% opacity cloning can smudge, as well as blend different work areas into each other. Plus, I think that our eyes do find film grain a pleasing artifact.
Here's a 100% example from yesterday's image. First is the original RAW file, second is hair removed and skin smoothed, and third is the a layer of film grain added. Pretty cool eh?