Check out an examination of photos taken with the pocket megazoom Canon PowerShot SX120 IS.
Noise starts to show at ISO 400, but up until that point photos are relatively clean and sharp with very good detail. At ISO 800 photos have a mottled look, but loss of detail is still fairly minimal. Even ISO 1,600 photos are usable as long as you can overlook some color change from increased noise levels.
This is a demo of the SX120's 10x optical zoom power. On the left is the lens at its widest position; the right is at its longest. The image stabilization does an excellent job of keeping the effects of hand shake under control.
Most newer cameras have some feature to help with backlit subjects or contrast extremes. Canon's i-Contrast does a respectable job bringing out the building detail without lightening the entire scene too much (left). It can be set to Auto while you're shooting or applied after the fact to different degrees. (I recommend the latter.)