ISO is a rating that refers to the "sensitivity" of the sensor. This means it directly affects how much light (or duration of exposure) the sensor will need to get the proper amout of exposure. Sensor settings of 100 ISO are not very sensitive and so require more exposure--for this default setting it is usually recommended that you shoot in bright sunlight.
Sensor settings at 400 ISO are more sensitive, require less exposure and can be used in more dimly lit situations, such as on overcast days, under a forest canopy and indoors.
Another consideration of ISO is that higher ISO settings are achieved by an amplification of the image signal from the sensor and will exhibit more noise or "graniness" and a "sandy" quality to the details. "Slower" ISO settings such as 100 will have less graniness and are therefore often preferred for enlargements. The degree of noise at various ISO settings depends on the manufacturer and the type of sensor. The image above was shot with a Canon SLR that uses a CMOS sensor. This camera is especially adept at rendering good detail at high ISO settings. Other cameras will produce even more noticable noise.
This means, that the best ISO setting is the slowest one that you can use. Again, this is why a tripod is so useful. I generally shoot all my nighttime photographs at ISO 100. This is not because ISO 100 is very capable in low light, but instead it is because I am after the best image quality AND I have a tripod!