October 1, 2020
Hot Topics:

What's new in SQL Server 2005

  • By Mike Gunderloy
  • Send Email »
  • More Articles »

You've certainly heard the news by now: Microsoft is on the verge of releasing a new version of their flagship enterprise-ready database, SQL Server. In fact, they announced at this year's Tech Ed that SQL Server 2005 (formerly code-named "Yukon") would be released to manufacturing the week of November 11. So, even if you haven't been paying much attention yet, it's time to start now. Of course, no one in their right mind switches to a new version of a server database overnight. But we're at the point in the development cycle where it makes sense to start considering whether it's worth your while to upgrade. A big part of your decision should be based on whether you need the new features offered by the new version. That's the reason for this article: to give you a quick rundown of the major areas of improvement, so you can decide if it's time to investigate further.

One note: I'm picking and choosing the features that I consider the most significant in this list. There are literally hundreds of new features in this release. After all, Microsoft has been working on ir for half a decade, and that's a long time in software years.

Begin at the Beginning

I'll start with the SQL Server database engine itself. Though there are a lot of other pieces in the SQL Server box (and I'll talk about most of them later in this article), without the ability to store and retrieve data they're not worth a whole lot. Fortunately, the SQL Server team lavished a lot of attention on the core of the product this time around. Here are some of the major improvements and new features you'll find in the database engine:

  • .NET Common Language Runtime integration: This means you can write stored procedures, functions, triggers, user-defined types, and even new aggregates in any .NET language.
  • Native HTTP SOAP Access: This means that the database server can serve up data via Web Services with no additional middleware at all, not even an instance of IIS.
  • New xml data type: You can store XML data natively in its own column type now.
  • XQuery support: SQL Server supports XQuery for querying against XML data, including extensions that allow XML DDL operations.
  • Improved Error Handling: T-SQL now supports TRY-CATCH blocks for modern error handling.
  • DDL Triggers: You can set up triggers that fire when CREATE, ALTER, or DROP statements are executed.
  • Query Notifications: With query notifications, an external application can request the database engine to notify it when the data in a cached result set changes. This can dramatically lessen the load from requerying.
  • Failover clustering now extends to 8 nodes on 32-bit systems, and 4 nodes on 64-bit systems.
  • Database mirroring introduces a new reliability model for hot standby on a database-by-database basis.
  • Users now have acess to a database during a restore operation, within certain limits. This helps get you up and running faster after problems.
  • Multiple Active Result Sets (MARS): SQL Server 2005 introduces the ability for multiple statements to return result sets at the same time on a single connection.

Crunching the Data

Turning from online transaction processing to online analytical processing, the people who work on SQL Server Analysis Services haven't been idle either. Here are a few of the enhancements you'll find in SQL Server Analysis Services (SSAS):

  • Like SQL Server itself, SSAS now supports multiple instances on the same physical computer.
  • The new Analysis Services Scripting Language provides an XML-based syntax for administering and scripting instances of SSAS.
  • The new Business Intelligence Development Studio provides a modern IDE (similar to Visual Studio .NET) for developing solutions using SSAS objects.
  • Multiple Fact Tables: One cube can now contain more than one fact table.
  • Perspectives are pre-defined subsets of cube metadata, similar to SQL Server views. You can use perspectives to reduce the perceived complexity of a cube for certain users.
  • Data mining has been enhanced with several new algorithms as well as a new wizard and editor for building and modifying data mining models.
  • SSAS is fully integrated with the .NET Framework for management and development.

No More DTS

You won't find Data Transformation Services in SQL Server 2005 - at least, not by that name. That's because it's been upgraded so much that it now has a new name to boot, SQL Server Integration Services (SSIS). Here's some of what lurks in that corner of the new product:

  • An extensible object model that lets you build new SSIS tasks and other components using the .NET Framework.
  • The new Business Intelligence Development Studio provides a modern IDE (similar to Visual Studio .NET) for working with SSIS objects (yes, this is the same IDE used by SSAS).
  • New workflow features that make it easier to create complex packages with looping and branching.
  • New tasks that can interact with WMI, files, Web Services, XML, and data mining queries.
  • New transformations including union all, merge, sort, pivot, unpivot, and sampling.
  • A new package deployment utility that makes it easy to move packages to another instance of SQL Server.

New Technologies

SQL Server 2005 bundles three new technologies as part of the standard database deployment. Two of these (Notification Services and Reporting Services) are available as add-ons to SQL Server 2000; the third is brand new.

The new technology is Service Broker, which Microsoft describes as "a new technology for building database-intensive distributed applications that are secure, reliable, and scalable." Built into the database engine, Service Broker lets you store message queues in a database. It also provides T-SQL statements that let you set up communications channels and then use those channels to send and receive messages. This gives you an asynchronous programming model and reliable messaging between SQL Server instances.

SQL Server Notification Services provides a platform for developing applications that send notifications, potentially to thousands or even millions of recipients. Notification Services provides a programming framework based on XML and T-SQL, and a high-performance engine to perform the actual notifications.

SQL Server Reporting Services is a server-based reporting technology that supports report authoring and distribution. A new Report Builder makes it easier than ever to create reports, and there are other tools than enhance the manageability of SSRS. With SSRS, you can create reports based on relational or multidimensional data from a wide variety of data sources.

Replicate That Data

SQL Server was an early entrant in server-based replication, and its support for replication has always been at the cutting edge. SQL Server 2005 doesn't disappoint in this regard, offering quite a number of new features and enhancements, including:

  • Improved monitoring features include a new Replication Monitor interface and the ability to inject tracer data into a complex replication topology to measure its latency.
  • A wide variety of schema changes are automatically replicated.
  • If a snapshot gets interrupted, it will resume from the point where it left off when connectivity is restored.
  • Oracle data can now be published to SQL Server subscribers without custom programming.
  • Transactional publications now support updates to large data types (varchar(max) and varbinary(max) columns).


Of course, there's the final list of new features that I couldn't avoid mentioning, but that don't quite fit anywhere else:

  • The new SQL Native Client replaces the old MDAC stack for client data access, and fully supports all the new database engine features.
  • SQL Server Management Studio provides a spiffy new IDE to replace the aging Enterprise Manager and Query Analyzer for all of your SQL Server administrative needs.
  • Books Online has had a complete facelift, and has a bundle of new tutorials and other helpful content.
  • The new sqlcmd utility replaces both osql and isql, and uses OLE DB to communicate with the database engine.

Sounds Great! What Now?

If you've gotten this far, you may be itching to get your hands on a copy of SQL Server 2005 to see the new functionality for yourself. The good news is that as of Tech Ed, this is easy: Microsoft just released a new Community Technology Preview (CTP) build of SQL Server for public download. So (assuming you've got a decent Internet connection), trying out the goodies is just a click of the mouse away. But remember, please: this is a beta. Don't put it on your production servers just yet! So far, I'm finding the June build to be pretty darned stable; it's definitely ready for most organizations to evaluate now. I look forward to rolling out a production server early next year.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest books, Coder to Developer (from which this article was partially adapted)and Developer to Designer, both from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.

This article was originally published on June 13, 2005

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date