Whether and when to upgrade to SQL Server 2008? It seems that there is still a school of thought that the release version of a product is not to be trusted and that the best policy is to "wait for SP1". Now this has never made much sense to me, not least because Service Packs can mean quite different things in different products. Sticking with SQL Server 2008 though, I believe there are plenty of reasons to upgrade now and NOT to wait for SP1.
Firstly, SQL Server 2008 is an extremely high quality and stable product and has already notched up a great many server-years of collective testing, both before release and after. I personally have been developing and testing with it on projects for a year now and it has been the best quality preview version of the product I've ever worked with. SQL Server 2008 RTM is in very much better shape now than SQL Server 2005 was at this point in its life.
Secondly, it has to be said that the history of SQL Server Service Packs is not an entirely happy one. There have been some very unfortunate problems with SQL SPs. For sure, the SQL Server team will learn from their mistakes but the fact is that an SP is never going to get the level of testing at customer sites that a major release gets. This is natural enough because SPs rarely contain new functionality so it's harder to justify the investment needed to build and test with the beta versions of SPs.
In terms of quantifiable risk then, installing a 2008 service pack right after release is potentially far more risky than installing SQL Server 2008 RTM right now.
Thirdly, there are the real returns you could get from upgrading to 2008 even with minimal effort. Upgrading from 2000 to 2005 was and is a big undertaking. Between 2000 and 2005 there is a whole raft of new features, deprecated features, breaking changes - all of which adds up to a lot of regression testing and potential development effort to take advantage of new features. In comparison, the additional effort to move from 2005 to 2008 or from 2000 to 2008 is a far smaller piece of work. Fewer new things will be broken or need re-working. Plus you can take advantage of new features like compression and policy based management with zero change to existing applications.
Andrew Fryer says that you may have to wait a long time for a SQL 2008 SP. At the UK User Group meeting last night he also had a suggestion. Even if you aren't upgrading your servers yet, at least install and use 2008 Management Studio so that you can take advantage of the new features in the tool!