Update SQL Server 2000 to SQL Server 2008: Benefits please?

Question

I'm looking for the benefits of upgrading from SQL Server 2000 to 2008.

I was wondering:

• What database features can we leverage with 2008 that we can't now?
• What new TSQL features can we look forward to using?
• What performance benefits can we expect to see?
• What else will make management go for it?

And the converse:

• What problems can we expect to encounter?
• What other problems have people found when migrating?
• Why fix something that isn't (technically) broken?

We work in a Java shop, so any .NET / CLR stuff won't rock our world. We also use Eclipse as our main development so any integration with Visual Studio won't be a plus. We do use SQL Server Management Studio however.

Some background:

Our main database machine is a 32bit Dell Intel Xeon MP CPU 2.0GHz, 40MB of RAM with Physical Address Extension running Windows Server 2003 Enterprise Edition. We will not be changing our hardware. Our databases in total are under a TB with some having more than 200 tables. But they are busy and during busy times we see 60-80% CPU utilisation.

Apart form the fact that SQL Server 2000 is coming close to end of life, why should we upgrade?

Any and all contributions are appreciated!

Answer 1

Besides all the features MatthewPK mentions, I also really like:

1. Common Table Expressions (CTE) (which I find extremely helpful) - see Using Common Table Expressions, SQL Server CTE Basics or SQL Server 2005 Common Table Expressions for more details

2. Ranking functions like ROW_NUMBER, RANK, DENSE_RANK and NTILE - see Ranking Functions (on MSDN) or New Ranking Functions in SQL Server 2005 for more details

3. OUTPUT clause in SQL statements to output information about e.g. rows you've deleted with the DELETE statement, or updated with your MERGE statement - see the SQL Server Books Online for more details.

I'm taking care of an old SQL Server 2000 solution, and boy, how many times have I missed those features!

Answer 2

There are a number of reasons to make the migration, I'm sure. My favorites are:

New DATE datatype (no more having to format strings to compare timestamped dates)

New Spatial Data types (geometry, geography)

New MERGE statement is great for upserts or any other "if exists" type logic

FILESTREAM gets you out of the blob problems (enforced DB integrity on filesystem directories!)

IMHO, from a developer's perspective, the most important upgrade is the TVP

The only shortfall I've personally encountered is that I had to rewrite my DTS packages to SSIS packages (but I think SSIS is great... just more work)

Answer 3

From a purely practical perspective, the most compelling advantages for me are several powerfull TSQL commands that are not available in 2000, e.g. PIVOT/UNPIVOT, and the addition of the intelligent syntax expansion to the 2008 Management Studio that made working with this tool substantially more productive.