When upgrading from SQL Server 2005 to SQL 2008, your gains will depend on what edition you're using. A lot of the really cool features in SQL 2008 are restricted to Enterprise Edition.

SQL Server 2008 Management Studio New Features

In this video, we show some of the killer features: http://tutorials.sqlserverpedia.com/SQLServerPedia-20090202-SSMS2008.flv

Upgrading from SQL Server 2005 Standard to 2008 Standard

New Datatypes

Some of the notable new datatypes in 2008 include:

• DATE - stores just the date, not both the date and time like the DATETIME field.
• TIME - stores just the time.
• DATETIME2 - like DATETIME, but with a dramatically higher accuracy.

Filestream Support

Instead of storing binary files inside the database, DBAs can store the files outside of the database on an NTFS partition.

Upgrading from SQL Server 2005 Enterprise to 2008 Enterprise

Enterprise includes the features in Standard, so the features we'll discuss below are above and beyond the feature gains in Standard.

Backup Compression

When doing database backups, the DBA can enable compression with a simple parameter on the BACKUP command. The resulting backup file is compressed as much as 90%, depending on the types of data being backed up. This works for full, differential and transaction log backups.

Compressed backups can only be done on Developer and Enterprise Editions of SQL Server 2008, but they can be restored on any edition, including Standard.

Data Compression

SQL 2008 offers the ability to compress tables and indexes at the object & partition level. When implemented, this feature can pay off in a 20-40% reduction in storage load for a fairly small increase in CPU load. Your performance will depend on the types of data you're storing: binary data like Sharepoint databases tends to not compress very well.

Data compression is configured for each individual table and index, so database administrators need to be aware that there's some configuration work to do in order to gain this benefit.

Data compression can only be done on Developer and Enterprise Editions. If a database with any compressed object is restored onto a Standard Edition server, the restore will fail.

Resource Governor

The Resource Governor lets DBAs throttle users or programs so that they cannot consume more than X% of a server's CPU or memory. This is especially helpful in consolidated environments with lots of applications on a single server. Here's a video explaining the basics of the Resource Governor:

http://tutorials.sqlserverpedia.com/SQLServerPedia-20090709-ResourceGovernor.flv