I absolutely believe it is useful to upgrade from SQL 2005 Standard to 2008 Standard. The warehouse I manage has gone from 7.0, 2000, 2005 to 2008. Going to 2005 was a lot of effort for the developers (because of lots of DTS and Analysis Services), but it made the upgrade to 2008 painless. We noticed an obvious difference in performance across many reports when we went to 2005. The change when we upgraded to 2008 was even more obvious. Same hardware. Still x86. Most reports use cubes as the source.

Building cubes in BDS is much nicer now, and Reporting Services 2008 is sweet. I love finally having a native Date type. Intellisense - have Red-Gate at work so don't need it there, but it's darn handy at home.

My comments are mostly subjective, but I can say unequivocally that those of us in my organisation, and at our vendor, are very pleased that we went to 2008. It didn't feel like a small change at all. Whereas 2000 to 2005 felt like we were rebuilding the engine (hard, dirty work), 2005 to 2008 felt like getting a tune-up, new tires and a paint job all in one.