Why should I upgrade from SQL2000 to SQL2005?

Question

I'm looking for the single biggest reason you are glad that you've already made the jump from SQL2000 to SQL2005.

Answer 1

Native XML support is big for us here.

Answer 2

SSIS support. Blows DTS away and is quite handy. :)

Answer 3

• Recursion without creating temporary tables.
• Native Exception support (Try/Catch instead of if @Error goto)

Answer 4

Because:

Microsoft would like to remind customers that support for SQL Server 2000 Service Pack 3a (SP3a) will end on July 10, 2007.

Answer 5

Pagination without (manually) creating temporary tables is a basic, but huge improvement.

However, if you are then going to drag & drop some GridViews in your ASP.NET app directly from the data table, you'd be paging in the app...

Answer 6

CLR integration

Answer 7

Row Versioning-Based Transaction Isolation

Answer 8

I think the single biggest reason is that SQL 2000 is not supported on Vista. I had to move to SQL 2005 because of that.

Answer 9

I don't know if it's just me, but Linq2SQL doesn't exactly work perfectly with SQL 2000. Ordinarily its supposed to automatically generate and populate child collections based on inferences from your schema's keys, foreign keys, RI, etc. Works fine in 2005 but i haven't had much luck in 2000.

Answer 10

Common Table Expressions have proven incredibly useful.

Answer 11

YOU CAN'T EXPORT TABLES with a "right click" anymore. This is more of a problem than a good reason.

Answer 12

1. SSRS - A really huge advantage for my organization is having the free reporting tools that come with SQL Server 2005. Reporting Services allows me to produce nice looking reports that have exactly the fields that our managers need in very little time. It has a built in tool so they can convert to excel, pdf, or several other formats. Lots of value here.

2. SSIS - Integration services in 2005 is very powerful for ETL (export, transform, load) functions. You can set up automated processes to run on a schedule.

3. SSAS - Analysis services looks promising. I have not made any data cubes yet because I want to organize an actual data warehouse. Once you have that, robust data mining algorithms are already built in.

Take a look at these three tools that are included with SQL Server 2005. If i had to pick one as the single biggest reason to move to 2005, it would be SSRS.

At this point, I would suggest looking at SQL Server 2008.

Answer 13

Exception handling... how did we ever manage on SQL 2000...?

Answer 14

Dynamic Management Views for Optimisation and to quickly find out the state of the server.

Answer 15

PIVOT. That beautiful little statement has saved me more time then any other SQL Server 2005 enhancement.