First off, here's my take on the science. Noise occurs for a variety of reasons, but the major two are statistical sampling errors and electronic interference. All a camera sensor does is count the number of photons of a given colour that impact upon a photo-receptor during the duration of the exposure. Electronic noise comes from adjacent receptors interfering with each other and the inherent baseline signal sampling that occurs. I am assuming that this is pretty constant, although if the sensor gets hotter in a longer exposure it will increase with the heat. When taking an image in low light there are two problems to contend with, both due to the fact that only a small number of photons are arriving at the sensor. Firstly this number may generate a signal comparable with the sensor noise and so the light is essential invisible to the camera, secondly at small numbers the statistical variance in photon incidence between sites illuminated in the same way will be relatively high. The first issue can be helped by progressive improvements in sensor design, the second is pure and simple physics and can only really be influenced by increasing the size of the sensor cell, i.e. by reducing the number of pixels for a given area. Software can help by averaging across multiple sites at low light intensity, but loss of detail is a price that must be paid.
So much for my understanding of the physics, what about my images. For this exercise I have chosen to use my EOS 5D Mark II, which has pretty good noise handling and a wide ISO range from 100 to 25,600. Other cameras would exhibit very different performance and in fact my choice of camera for a specific task is partly driven by noise handling. If I know that I will need to use an ISO greater than 400, then the only camera I use is the 5D2, my 7D works fine to ISO-400, whilst the G11 and 40D tend to be happier at ISO200 and below.
For the test I have used an indoor still life with only natural light. The subject is a flower placed against a white card, such that there are areas of light and shade on the card. This is the first photo
The colours are dull as the light was very flat. I also simply used matrix metering and did not do any correction for the fact that I was shooting a predominantly white subject, were I doing this to create a lasting image, I would have over-exposed by 1-2 stops to bring the whites back from grey. For this exercise that is not an issue. The lens is a 135mm f/2 set at f/2. This gives an exposure of 1/8s at ISO100. I then progressively increased the ISO, from 100 all the way to 25,600. I imported the images into Lightroom and imaged a portion at 100%:
The other issue here is the pre-processing that all RAW converters do, in my case Lightroom 3 presents a much better image than the Canon software that came with the camera, however, all RAW photographs must be processed and as a result this exercise must be considered in the context of the image sensor and RAW workflow usually used by the photographer in question.