Noise in digital images is basically akin to the 'grain' obtained by using a high ISO film. The higher the ISO, the more the camera amplifies the signal received from the photo sites in the sensor. As the signal is amplified, so is the background electrical noise present in almost any electrical system. When shooting at low ISOs this typically isn't a problem, but it depends a lot on what camera body you are using.
The more technology advances, the better high ISO performance is getting in today's newest DSLRs. Nikon's D3, Canon's 1D Mark III, and their previous high ISO king, the 5D, all allow much cleaner images up to 1600/3200/+ now. I've produced some exceptional shots at 3200 and 6400 with the 1D Mark III... I've been very impressed at how clean (relatively speaking) these images come out of the camera.
Scott