SQL Server SP2 was released last Monday. The links were posted on many blogs, so I won't repost any but the main one here. SP2 is a "major" service pack because there are a few "must have" features in addition to some rather useful improvements and quite a few bug fixes. The ones I'd consider must haves are:
1. Support for running SQL Server 2005 on the Windows Vista operating system. Vista will inform you of the SP2 requirement when you install SQL Server 2005. You install the "base" SQL Server 2005 first and apply SP2 immediately afterward, except with SQL Server 2005 Express. Express ships an SP2-specific product.
2. Support for business intelligence features of Office 2007 in Analysis Services. Unless you have SP2 installed, these features will be greyed-out in Office 2007.
There are some brand new features too like Sharepoint Server 3.0/SQL Server Reporting Services integration, the vardecimal storage format, and multi-schedule/multi-server Database Maintanance Plans.
But with that many changes, there are bound to be some incompatibilities. Be careful with these.
1. If you enable vardecimal storage on a database, you can't restore or attach an SP2-post vardecimal format database backup on an SP1 instance.
2. You can't use SP2 Analysis Services backups and data folders on pre-SP2 instances of Analysis Services
3. Analysis Services databases with linked measure groups can have problems on SP2. The most likely ones will be databases migrated from AS2000. See the SP2 Readme file for specifics.
4. You can't use Database Maintanance Plans from an SP2 instance will an SP2 instance, because the SP2 maintanance plans can have multiple schedules.
So be sure and be aware of these to have a successful upgrade.