What are the Differences Between SQL Server 2000 and SQL Server 2005?
In part I of this series I looked at the administrative differences and in this part I'll cover some of the development differences between the versions. I'm looking to make a concise, short list of things you can tell a developer who is interested, but not necessarily knowledgeable about SQL Server, to help them decide which version might be best suited to meet their needs.
And hopefully help you do decide if an upgrade is worth your time and effort.
One short note here. As I was working on this, it seemed that there are a great many features that I might put in the BI or security space instead of administrator or development. This may not be comprehensive, but I'm looking to try and show things from the main database developer perspective.
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. |
Conclusion
These are the highlights that I see as a developer and that are of interest. There are other features in the security area, scalability, etc. that might be of interest, but I think these are the main ones.
I welcome your comments and thoughts on this as well. Perhaps there are some features I've missed in my short summary that you might point out and let me know if you think it makes sense to discuss some of the security changes. As far as BI stuff, hopefully one of you will send me some differences in an article of your own.