DatabaseWhat's new in SQL Server 2005

What’s new in SQL Server 2005 content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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
  • 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
  • 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
  • Multiple Fact Tables: One cube can now contain more than one fact
  • 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
  • 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

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

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

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.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories