SQL Server 2005 secrets

There's plenty of hype about the new SQL Server 2005. Here's a list what's important about the pending release, and what you can plan on using SQL Server for in the near future.

The fanfare from the US 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 optimised, 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 organising 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.

TechRepublic is the online community and information resource for all IT professionals, from support staff to executives. We offer in-depth technical articles written for IT professionals by IT professionals. In addition to articles on everything from Windows to e-mail to firewalls, we offer IT industry analysis, downloads, management tips, discussion forums, and e-newsletters.

Ã,©2005 TechRepublic, Inc.

Dim tr As Table = server.Databases("db").Tables("tbl", "dbo")

For Each st As String In  tr.Script()

            File.AppendAllText("C:\auto.txt", st & ControlChars.NewLine)

        Next