Benefits of SQL Server 2005 over 2000

Question

Could somebody please name a few. I could given time, but this is for somebody else, and I'd also like some community input.

Answer 1

CLR stored procedure support

Answer 2

Better clustering/replication facilities

Answer 3

It depends if you're talking about just the DB engine or the product as a whole. SQL Server 2000 didn't have anything like Analysis services and Reporting services. As for the DB engine - if you use it for a simple application, I don't think you should bother. Also, I think the SQL Express edition introduced in 2005 became valuable for one-man-show companies and investigation/testing small projects

Answer 4

SQL Express has larger database capacity than the previous MSDE product (4Gb vs 2Gb), so it's great as Slavo said for testing and small apps. SQL Server 2005 is updated via Microsoft Update, which is good or bad depending on your point of view.

Answer 5

Some differences:

• CLR (.NET) stored procedures
• SSIS instead of DTS
• Management Studio instead of Enterprise Manager, with more functions (2008 version is even better)
• VS integration
• better replication
• SMO and AMO (extensions to handle the server from applications)
• table and index partitioning
• XML as data type
• XQuery to handle XML data type
• Service Broker
• Notification Services
• Analysis Services
• Reporting Service

I have now these ones in mind. There are a lot of other small nice stuff, but I cannot name more.

Answer 6

Also, Common Table Expressions and exception management in TSQL. Very useful.

Answer 7

Schemas - Okay, 2000 has owners, but they can be a real pain to get permissions right on.

Answer 8

Two things make it much better for me:

1 - Great XML support.

2 - Partitioned Tables. No more multiple-tables and views - just define your partition schema and you can easily manage HUGE tables with far improved performance.

Answer 9

Snapshot Isolation Also known as readers don't block writers.

Answer 10

The Data Type varchar(MAX)

In SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size ... To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used.

From http://www.teratrax.com/articles/varchar_max.html

Answer 11

Something very important is the TRY CATCH statement - SQL2005 supports such statement while SQL2000 does not.

sample:

BEGIN TRY
    -- Generate divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    -- Execute custom error retrieval routine.
END CATCH;

Answer 12

The PIVOT/UNPIVOT operators have been a big win for me.

PIVOT rotates row-based output into columns, which is a huge help in a lot of our reporting needs. We had to roll our own functions for PIVOT prior to SQL 2005.