CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2000
ProfK
ProfK

Reputation: 51124

Benefits of SQL Server 2005 over 2000

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

Upvotes: 3

Views: 1663

Answers (12)

Jerry
Jerry

Reputation: 163

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.

Upvotes: 0

JohnIdol
JohnIdol

Reputation: 50137

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;

Upvotes: 1

Seibar
Seibar

Reputation: 70373

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

Upvotes: 2

Jim T
Jim T

Reputation: 12436

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

Upvotes: 2

Valerion
Valerion

Reputation: 831

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.

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

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

Upvotes: 1

Santiago Cepas
Santiago Cepas

Reputation: 4104

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

Upvotes: 3

Biri
Biri

Reputation: 7181

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.

Upvotes: 5

Mike Edwards
Mike Edwards

Reputation: 442

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.

Upvotes: 0

Slavo
Slavo

Reputation: 15463

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

Upvotes: 0

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340496

Better clustering/replication facilities

Upvotes: 1

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340496

CLR stored procedure support

Upvotes: 1

Related Questions