The fanfare
from TechEd 2005 this past June was
vintage Microsoft: lots of happy noise about miraculous products with
tongue-in-cheek delivery dates, as many of us suppressed a yawn. But let’s give
credit where it’s due; we’ve waited a long time for a new SQL
Server, and the features Microsoft is delivering with its upgraded database
technology are what we’ve asked for and then some. Here’s a rundown on the
features we’ll find most useful.

1. There is now an XML data type

If there’s
any feature of SQL Server 2005 to jump up and down about, it’s the new native XML data type.
Why? Apart from the giant leap forward of an already Web-friendly agenda, the
new type offers us design options that are atypical of Microsoft, which
generally likes to do our designing for us.

The new XML
data type:

  • can be used in a table column
  • can be used in a stored
    procedure, as a parameter or as a variable
  • can store untyped
    data
  • can check against a schema to
    see if data stored in a column typed as XML matches that associated schema
    (if there’s no schema, the data is considered untyped)

And the
mapping between XML data and relational data is bidirectional.

2. Distributed Management Objects (DMO) becomes SQL Server Management
Objects (SMO)

SQL Server
Management Objects (SMO) is a .NET
Framework-based management framework that lets you create custom
applications for server management. SMO (like DMO before it) allows you to
handle columns, tables, databases, and servers as objects, programmatically—and
SMO supports SQL Server 2005’s new features, like Service Broker.

SMOs are
optimized, not instantiating objects fully (with all the properties retrieved)
until the object is explicitly reference. You can also batch SQL commands, and
create scripts to create objects.

Your custom
server management apps can be used to manage SQL Server 7 in SQL Server 2000
systems as well.

3. Common Table Expresssions (CTEs)—recursive
queries

A common
table expression (CTE) enables queries to be recursive. A CTE can be
self-referential, with an upper limit on the incursions. You can use the CTE as
a part of a WITH, in a SELECT, UPDATE, INSERT or DELETE command.

4. The Service Broker makes SQL Server traffic asynchronous

There’s a
front-end queuing system, and it changes everything. You can now manage SQL
Server traffic by rendering it asynchronous with the new Service Broker
feature. It enhances scalability by enabling your system to handle more traffic
logically that it can handle physically. The Service Broker can be accessed via
SQL commands and allows transactions to include queued events.

Those who
know me well would never accuse me of being a Microsoft disciple, but this
feature impresses me in no small measure and I’m pleased to call attention to
it. Adding easily-configured asynchronicity
to the data layer of an enterprise system is a boon to developers and opens up
huge possibilities for Web apps. The economy with which those apps can now
scale can’t be overstated. Service Broker alone is a reason to consider
upgrading to SQL Server 2005.

5. Create .NET triggers

SQL Server
2005 is .NET-integrated to a promising degree (it has distressed us for some
time that Microsoft’s commitment to .NET is as hedged as it is), and one useful
consequence of this integration is the ability to create user-defined triggers
(UDTs) through Visual Studio 2005.

The Trigger
option can be pulled from the template list in Visual Studio, generating a file
for the code to be triggered. The mechanism tying this code to SQL is a SqlPipe. It’s deployed in your Build | Deploy. You can work
it in the other direction (i.e., from CLR) by referencing the Trigger object in
a T-SQL CREATE TRIGGER command.

6. SQL Server 2005 configuration is dynamic

If you’re
running SQL Server 2005 on Windows Server 2003, its configuration is fully
dynamic—you can change configuration values on-the-fly without restarting the
server, and get immediate response (the same is true for Address Windowing
Extensions).

7. Define your own data types

The
user-defined type, enabled by the integration of SQL Server 2005 and the .NET
CLR, is a consolidation of previous practices, allowing you to create
application- or environment-specific types. You can extend more general types
into variations that only except values you define—no more triggering or
constraints. Validation is built into the field.

8. Many active result sets, one connection

This is
another feature not just to make note of, but to get excited about. MARS (Multiple
Active Result Sets) enables you to execute multiple queries yielding
multiple results, over a single connection. An application can move between
open result sets as needed. The performance and scalability benefits are
obvious.

This new
trick is courtesy of the new ADO.NET, in tandem with SQL Server 2005’s ability
to accommodate multiple active commands. Since MARS is part SQL Server 2005 and
part ADO.NET 2.0, it is only available if you’re using both.

9. WAITFOR … RECEIVE

In previous
versions of SQL, WAITFOR was static. We fed it some wait-time value, and that
was what it could do. Now WAITFOR is dynamic; tell it to wait for a RECEIVE
statement’s results, whenever that might be delivered.

Beyond the
usual this-is-cool, we can appreciate this feature because of the manner in
which it accommodates the new Service Broker (see #2). Since Service Broker
makes database query ability asynchronous via queuing (and therefore extremely
dynamic), and a particular database query may sit in a queue for an
undetermined period, the new dynamic WAITFOR his ideal for responding to
RECEIVE results that will emerge at the discretion of Service Broker.

10. DTS is now Integration Services

There’s a
new architecture underlying data transformation. The very popular and widely
used DTS is now Integration Services, and consists of a Data Transformation
Pipeline and a Data Transformation Runtime.

The
pipeline connects data source to data target by means of data adapters, with
transformations between them. It’s a conventional structure, but implemented in
such a way as to enable considerable complexity: for instance, you can do
one-to-many mappings, and create columns with output derived from a transform.

The Data
Transformation Runtime gives you components for organizing data loading and transformation
processes into production-oriented operations, within which you can manage
connections and manipulate variables. It’s basically a run-time object
framework that can be bundled into managed .NET apps.

DTP and DTR
components are used to create Integration Services packages, similar in
principle to the familiar DTS packages but with much greater levels of
configurability and control, particularly in the area of workflow.