When I was rotating my pictures last time with windows image preview thingy I realized that this could already been done by my N900 when I took the picture with the built-in camera with the help of the accelerometer.
Aren't all pictures taken in portrait supposed to be viewed also in portrait? What do you think?
I guess it is because rotation would slow down the process of taking images on the phone, and that it is better to save the rotation info only in the image metadata (EXIF). There are several tools out there to let you rotate images physically based on the EXIF data, just google "EXIF rotate physically" and look for some for your OS.
The only place I think it would be useful with auto-rotation is while uploading pictures for sharing on for instance Facebook or Flickr.