MS SQL 2000 vs SQL 2005

Could someone please give me some real world differences between MS SQL 2000 compared to MS SQL 2005?  

I am considering upgrading to SQL 2005, but need to justify the cost.

Notes:  I am a noob with SQL, as we currently run MS SQL 200 for three of our critical business applications on SBS 2003.

Thank you!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
>>I am considering upgrading to SQL 2005

You should consider upgrading to SQL 2008.
The Administrative Differences
Administering a SQL Server instance to me means making sure the server service runs efficiently and is stable and allows clients to access the data. The instance should keep data intact and function according to the rules of the code implemented while being well maintained.

Or for the non-DBAs, it means that you are the sysadmin and it just works.

The overall differences are few. Sure we use Management Studio instead of Enterprise Manager, but that's not really a big deal. Really many of the changes, like being able to change connections for a query, are superficial improvements that don't really present a substantial change. If you think they do, you might be in the wrong job.

Security is one area that is a very nice improvement. The separation of the schema from the owner makes administrative changes easier, but that is a big deal because it greatly increases the chances you won't keep an old account active because it's a pain to change owners on objects. There's also more granularity and ease of administration using the schema as another level of assigning permissions.

Another big security change is the ability to secure your web services using certificates instead of requiring authentication using a name and password. Add to that the capability to encrypt data, and manage the keys, can make a big difference in the overall security of your data. You have to carefully ensure your application and access is properly secured, but just the marketing value of encryption when you have credit card, financial, or medical data is huge. SQL Server 2000 had no real security features for data, allowing an administrator to see all data. You could purchase a third party add-on, but it was expensive and required staff training. Not that you don't need to learn about SQL Server 2005, but it should be a skill that most DBAs will learn and be able to bring to your organization over time.

High availability is becoming more and more important to all sizes of businesses. In the past, clustering or log shipping were your main choices, but both were expensive and required the Enterprise Edition. This put these features out of the reach of many companies, or at least, out of many DBAs' budgets. With SQL Server 2005, you can now implement clustering, log shipping, or the new Database Mirroring with the Standard edition. With the ability of Database Mirroring to use commodity hardware, even disparate hardware between the primary and mirror databases, this is a very reasonable cost solution for almost any enterprise.

There are also online indexes, online restores, and fast recovery in the Enterprise Edition that can help ensure that you take less downtime. Fast recovery especially can be an important feature, allowing the database to be accessed as the undo operations start. With a log of open transactions when a database is restarted, this can really add up to significant amounts of time. In SQL Server 2000, you had to have a complete, intact database before anyone could access it. With redo/undo operations sometimes taking a significant amount of time, this could delay the time from Windows startup to database availability by minutes.

Data sizes always grow and for most companies, performance is always an issue on some server. With SQL Server 2000, you were limited to using 2GB of RAM and 4 CPUs on the Standard Edition. The number of CPUs hasn't changed, but you can now use as much RAM as the OS allows. There also is no limit to the database size, not that the 1,048,516 TB in SQL Server 2000. Since RAM is usually a limiting factor in the performance of many databases, upgrading to SQL Server 2005 could be something you can take advantage of. SQL Server 2005 also has more options and capabilities on the 64-bit platform than SQL Server 2000.

Why Upgrade?
This is an interesting question and one I've been asked quite a bit over the last 18 months since SQL Server 2005 has been released. The short answer is that if SQL Server 2000 meets your needs, then there's no reason to upgrade. SQL Server 2000 is a strong, stable platform that has worked well for millions of installations. If it meets your needs, you are not running up against the limits of the platform, and you are happy with your system, then don't upgrade.

However, there is a caveat to this. First the support timeline for SQL Server 2000 shows mainstream support ending next year, in April 2008. I can't imagine that Microsoft wouldn't extend that given the large number of installations of SQL Server 2000, but with the next version of SQL Server likely to come out next year, I can see this being the point at which you cannot call for regular support. The extended support timeline continues through 2013, but that's an expensive option.

The other consideration is that with a new version coming out next year, you might want to just start making plans to upgrade to that version even if you're happy with SQL Server 2000. If the plan is to release a new version every 2-3 years, you'll need to upgrade at least every 5-6 years to maintain support options.

Be sure that in any case you are sure the application you are upgrading, if it's a third party, is supported on SQL Server 2005.

Lastly, if you have multiple servers and are considering new hardware for more than 1 of them, it might make some sense to be sure to look at buying one large 64-bit server and performing some consolidations. I might recommend that you wait for the next version of SQL Server if you are worried about conflicts as I have heard rumors of switches to help govern the resource usage in Katmai (SQL Server 2008).

A quick summary of the differences:

Feature SQL Server 2000 SQL Server 2005
Security Owner = Schema, hard to remove old users at times Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.
Encryption No options built in, expensive third party options with proprietary skills required to implement properly. Encryption and key management build in.
High Availability Clustering or Log Shipping require Enterprise Edition. Expensive hardware. Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.
Scalability Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support. 4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.

The Development Differences
Developing against SQL Server 2005 is in many ways similar to SQL Server 2000. Most all of the T-SQL that you've built against SQL Server 2000 will work in SQL Server 2005, it just doesn't take advantage of the newer features. And there are a great many new extensions to T-SQL to make many tasks easier as well as changes in other areas.

One of the biggest changes is the addition of programming with .NET languages and taking advantage of the CLR being embedded in the database engine. This means that you can write complex regular expressions, string manipulation, and most anything you can think of that can be done in C#, VB.NET, or whatever your language of choice may be. There's still some debate over how much this should be used and to what extent this impacts performance of your database engine, but there's not denying this is an extremely powerful capability.

The closest thing to this in SQL Server 2000 was the ability to write extended stored procedures and install them on the server. However this was using C++ with all the dangers of programming in a low level language.

However there are many new extensions to T-SQL that might mean you never need to build a CLR stored procedure, trigger, or other structure. The main extension for database developers, in my mind, is the addition of the TRY/CATCH construct and better error information. Error handling has been one of the weakest parts of T-SQL for years. This alone allows developers to build much more robust applications.

There are also many other T-SQL additions, PIVOT, APPLY, and other ranking and windowing functions. You might not use these very often, but they come in handy. The same applies to Common Table Expressions (CTEs), which make some particular problems very easy to solve. The classic recursion of working through employees and their managers, or menu systems, have been complex in the past, but with CTEs, they are very easy to return in a query.

One of the other big T-SQL additions is the OUTPUT clause. This allows you to return values from an INSERT, UPDATE, or DELETE (DML) statement to the calling statements. In an OUTPUT statement, just like in a trigger in SQL Server 2000, you can access the data in the inserted or deleted tables.

One of the programming structures that many developers have gotten more and more exposure to over the last decade is XML. More and more applications make use of XML, it's used in web services, data transfers, etc. XML is something I see developers excited about and with SQL Server 2005 there is now a native XML data type, support for schemas, XPATH and XQUERY and many other XML functions. For database developers, there is no longer the need to decompose and rebuilt XML documents to get it in and out of SQL Server. Whether you should is another story, but the capabilities are there.

There are a couple other enhancements that developers will appreciate. The new large datatypes, like varchar(max) allow you to store large amounts of data in a column without jumping through the hoops of working with the TEXT datatype.

Auditing is much easier with DDL triggers and event notifications. Event notifications in particular, allowing you to respond to almost anything that can happen in SQL Server 2005, can allow you to build some amazing new applications.

The last enhancement in T-SQL that I think developers will greatly appreciate is ROW_NUMBER(). I can't tell you how many times I've seen forum posts asking how to get the row number in a result set, but this feature is probably greatly appreciated by developers.

There are a number of other areas that developers will find useful. Service Broker, providing an asynchronous messaging system can make SOA applications a much easier to develop. Until now, this is a system that appears easy to build, but allows unlimited opportunities for mistakes. Native web services are also a welcome addition to allow you to extend your data to a variety of applications without requiring complex security infrastructures.

Reporting Services has grown tremendously, allowing more flexibility in how you deploy reports to end users. Integration Services is probably the feature that most requires development skills as this ETL tool now really is more of a developer than a DBA system. However with the added complexity, it has grown into an extremely rich and tremendously capable tool.

There are other changes with SQL Server, ADO.NET has been enhanced, Visual Studio has been tightly integrated with it's extensions for various features as well as its influence on the Business Intelligence Design Studio, and the Team System for DB Pros. The Full-Text Search capabilities have been expanded and they work better, allowing integration with third party word-breakers and stemmers as well as working with noise words.

Why Upgrade?
This is an interesting question. As with part I of this series, I'm not completely sure of how to recommend this. If your server is running well as an administrator, there's no reason to upgrade. As a developer, however, it's a bit more complicated.

Developers, almost by definition, are looking to change things on a regular basis. For developers, they are fixing things, enhancing them, or rebuilding them. In the first or even second case, it may not make much sense to upgrade if your application is working well. In the latter case, I'd really think hard about upgrading because a rebuild, or re-architecture, takes a lot of time and resources. If you're investing in a new application, or a new version of an application, then SQL Server 2005 might make sense to take advantage of the features of SQL Server 2005.

I'm guessing that many of these features will be around through at least the next two versions of SQL Server. While I can see there being a radical rewrite after Katmai (SQL Server 2008), I can't imagine that many things won't still be around in the version after that. They may get deprecated after that, but they should be there for that version, which should see support through 2018 or 2019.

If you are struggling with ETL, trying to implement messaging, or web services, then it also might make sense to upgrade your database server to SQL Server 2005.

A quick summary of the differences:

Feature SQL Server 2000 SQL Server 2005
Server Programming Extensions Limited to extended stored procedures, which are difficult to write and can impact the server stability. The incorporation of the CLR into the relational engine allows managed code written in .NET languages to run. Different levels of security can protect the server from poorly written code.
T-SQL Error Handling Limited to checking @@error, no much flexibility. Addition of TRY/CATCH allows more mature error handling. More error_xx functions can gather additional information about errors.
T-SQL Language SQL Language enhanced from previous versions providing strong data manipulation capabilities. All the power of SQL Server 2000 with the addition of CTEs for complex, recursive problems, enhanced TOP capabilities, PIVOT/APPLY/Ranking functions, and ROW_NUMBER
Auditing Limited support using triggers to audit changes. Robust event handling with EVENT NOTIFICATIONS, the OUTPUT clauses, and DDL triggers.
Large Data Types Limited to 8k for normal data without moving to TEXT datatypes. TEXT is hard to work with in programming environments. Includes the new varchar(max) types that can store up to 2GB of data in a single column/row.
XML Limited to transforming relational data into XML with SELECT statements, and some simple query work with transformed documents. Native XML datatype, support for schemas and full XPATH/XQUERY querying of data.
ADO.NET v1.1 of ADO.NET included enhancements for client development. v2 has more features, including automatic failover for database mirroring, support for multiple active result sets (MARS), tracing of calls, statistics, new isolation levels and more.
Messaging No messaging built into SQL Server. Includes Service Broker, a full-featured asynchronous messaging system that has evolved from Microsoft Message Queue (MSMQ), which is integrated into Windows.
Reporting Services An extremely powerful reporting environment, but a 1.0 product. Numerous enhancements, run-time sorting, direct printing, viewer controls and an enhanced developer experience.
ETL DTS is a very easy to use and intuitive tool. Limited capabilities for sources and transformations. Some constructs, such as loops, were very difficult to implement. Integration Services is a true programming environment allowing almost any source of data to be used and many more types of transformations to occur. Very complex environment that is difficult for non-DBAs to use. Requires programming skills.
Full-Text Search Workable solution, but limited in its capabilities. Cumbersome to work with in many situations. More open architecture, allowing integration and plug-ins of third party extensions. Much more flexible in search capabilities.

from SQL Server  Central
Kevin CrossChief Technology OfficerCommented:
And one way to justify the cost to management is the fact that SQL Server 2000 will be going end of life here shortly and so if you go with suggestion to leap frog a version to SQL Server 2008 you can show that you are positioning them for the next 10 years. :)

Mainstream support actually ended this year:

Good to get migration going early.  Especially if you can show that you can get good deal on licensing now through some deals or something: 

I would keep checking the incentives site as I usually see some Small Business Server and SQL Server incentives on there although there are none showing now.  Can also contact your license provider.  One of the things I did, which may not make as much sense now; however, I bought my SQL 2005 under an Open agreement so I could spread payments over 3 years which helped as my organization is a little smaller and they liked having an expense versus capital.  The caveat with this is you have to do software assurance in this option.  That was to my benefit with SQL 2008 on the horizon, so now I have SQL 2008 for free to upgrade to.  Therefore, you can do math against amount of SA you will pay until the next major release you would want to move to.  The amortized payments may be worth it regardless though and the agreements gets you access to some more things anyway.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Agree with the other opinions. There is no case at all for upgrading to 2005 unless you are forced to. Upgrade to 2008. The advantages over a decade old product that is no longer in mainstream support are almost too many to list. Check out

Do bear in mind that the upgrade will entain a certain amount of work testing and possibly fixing certain things. There are some breaking changes to deal with.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.