Shooting action in low-light conditions or freezing very fast action are common situations where you might need to compromise on the ISO to get the shot.
For example, I recently shot some pictures of jets passing by at an air show. I found that even at noon on a day with clear skies and the aperture at 2.8, ISO 100 was too low to get me the shutter times I wanted. I had to go to ISO 800 to get 1/8000 shutter times. Now, ISO 800 produces perfectly good results with my camera, but if I could have shot even faster shutter times than 1/8000 then I would definitely have tried out ISO 1600 too, as even 1/8000 wasn't always fast enough to freeze an F-15 buzzing the crowd.
Regarding high ISO for effects, I rarely find chroma noise pleasant to look at so I usually post-process high ISO pictures in black and white. While luminance noise from a digital sensor doesn't compare to the grain of high ISO black and white film, it sometimes provides passable results with a little post-processing.
Here's an example of a night time shot with ISO 6400. It's not a technically very good shot--the noise is very pronounced--but it's a shot I couldn't get any other way as I didn't bring any flashes with me that day.
(original)