March 3, 2021
Hot Topics:

What's New and Cool in SQL Server "Yukon"

  • By Peter W. DeBetta
  • Send Email »
  • More Articles »


Existing abilities such as the FOR XML clause and the OPENXML function have not changed much in this latest release of SQL Server. These two features were the first step in incorporating XML into SQL Server.

SQL Server "Yukon" has an XML data type. XML, much like varchar, stored a string of characters, but XML is obviously more than a string of characters — XML has structure. And with this structure comes with a host of other features. The first is the ability to query within the XML data using the query method. XPath expressions are part of the XQuery ability in SQL Server "Yukon", which supports a subset of the XPath expressions in the XQuery specification.

The XML data type also has the value method, for retrieving a specific value from the XML data, and the exists method, for determining node existence. Microsoft has also added a method called modify, which uses a Microsoft specific extension of XQuery that allows you to modify XML data in place, which can potentially yield better performance than fetching the XML data, modifying it and pushing it back into the database. The following is an example from SQL Server Books Online that shows the value method being used to pull the ProductModelID from the xml data type column, CatalogDescription, which is in the ProductModel table of the new AdventureWorks database.

SELECT CatalogDescription::value('    namespace PD=   "http://www.adventure-works.com/schemas/products/description"       /PD:ProductDescription/@ProductModelID ', 'int') as ResultFROM ProductModelORDER BY Result desc

Many volumes of books have been written on the topic of XML. As you can see, SQL Server "Yukon" will have a rich feature-set for managing XML data.

CLR Integration

Amongst the changes you will find in SQL Server "Yukon", the one that everyone is talking about is the .NET CLR integration. Yes, you will be able to write procedures and functions using a language such as C# or Visual Basic.NET in addition to the traditional T-SQL way. CLR code is not meant to be a replacement to T-SQL code, but rather, a compliment. Where T-SQL is best for directly manipulating data, CLR code is best for computational intense code.

CLR code can be used to as the code base for user-defined functions, stored procedures and triggers, or it can extend T-SQL with features such as user-defined types or user-defined aggregates. For example, let's say that you want to create a function to check whether a ZIP code value was a valid 5 or 9 digit ZIP code. Sure, you can create a user-defined function in T-SQL and check the ZIP code value using the Like comparison operator validating that it contains the correct number and type of characters.

CREATE FUNCTION dbo.fnCheckZip (zipCode varchar(10))RETURNS bitASBEGIN   IF zipCode LIKE '[0-9][0-9][0-9][0-9][0-9]' OR zipCode      LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'      RETURN 1   ELSE      RETURN 0END

Or we can write it using C# as follows:

using System;using System.Data.Sql;using System.Data.SqlTypes;public class CZipCheck{    [SqlFunc]    public static SqlBoolean ZipCheck(SqlString zipCode)    {        return System.Text.RegularExpressions.Regex.IsMatch(                 zipCode, @"^\s*(\d{5}|(\d{5}-\d{4}))\s*$");    }}

I know it looks overwhelming at first, but for all you .NET developers, this will come easy. We have new attributes that will be used in any .NET code that is to be registered in SQL Server. In this case, we see we are defining this method as a function of SQL Server using the SqlFunc attribute. We also have SQL Server specific data types at our disposal in addition to the existing data types of the various .NET languages. Finally, this user-defined function takes advantage of the CLR Framework that is not available to T-SQL. This gives you the ability to extend the code that you run in SQL Server's context to include much more complicated checks and rules.

This is one simple example of how the CLR Framework can be leveraged in creating user-defined functions, stored procedures, triggers, etc. In addition, this object is not only usable in SQL Server, but on your clients as well. The same assembly that is registered within SQL Server can be used in other applications. This is particularly useful when creating user-defined types in .NET because the object becomes part of the information passed to the data client, allowing the consumer to use the object's methods and properties.


So, where routines written in T-SQL use Data Manipulation Language (DML) to access and modify data, routines written using .NET languages use ADO.NET to do the same. And if you want to execute a stored procedure in T-SQL, you would use the EXECUTE statement, whereas in C#, you would use a SqlCommand object. This is no different than existing .NET and SQL Server abilities, with one exception: The in-process provider does not have to establish a connection, but rather, gets the existing one in which the code is being executed.

SqlConnection conn = SqlContext.GetConnection();SqlCommand cmdLog = conn.CreateCommand();

One of the abilities that goes hand-in-hand with the CLR integration is the integration of ADO.NET in SQL Server "Yukon". This in-process managed data provider provides special features that extend ADO.NET's ability to be used within .NET-based code. The SqlServer namespace is similar to SqlClient in that some of the standard objects, such as SqlCommand and SqlConnection are present, but it is, for the most part, a subset of the SqlClient objects, with the exception of several new objects including SqlPipe, SqlContext and SqlTriggerContext. Because the SqlServer managed provider run within the context of SQL Server itself, and not as a client which connects to SQL Server, its methods will outperform the equivalent SqlClient method.

These three new objects allow us to gain access to the current execution context within SQL Server and take advantage of it — something that client data access would not be able to do. SqlContext (as seen above) gives access to the current connection. SqlPipe allows us to get an existing pipe in order to send data back to the calling application. SqlTriggerContext gives us access to trigger specific information, such as the type of trigger being executed or information about whether columns are being updated.

.NET Summary

I am not advocating (nor does Microsoft) that you should now convert all your existing T-SQL code into its .NET equivalent. T-SQL is still the undisputed champ when it comes to data access performance. To sum things up: If your code simply needs to perform an update against a table, you should use T-SQL; and you use .NET when you need to do computational tasks, which can be done better — and faster — than T-SQL.

Other Objects and Services

In addition to everything you have read about, there are a variety of enhancements or additions at the service and programmability levels of SQL Server. Some of the existing features now have CLR integration and, in the some cases, some substantial enhancements. These changes can be found in the following:

  • Data Transformation Services
  • Replication Services
  • Analysis Services

There are also some new services which have CLR programming capabilities. SQL Service Broker is the new messaging and queuing mechanism of SQL Server "Yukon". It is more robust than MSMQ and has a managed code interface for easy development. Reporting Services is already available in SQL Server 2000, but will have an improved feature-set for SQL Server "Yukon" delivery. Reporting Services allows you to create, manage and deliver reports in a variety of output formats, including HTML, Excel, PDF, etc., to a variety of targets, including email and file shares. All this is done with a manage code interface and SQL Server as the data store for your reporting services.

And let's not forget SQL Management Objects (SMO), the replacement of Distributed Management Objects (DMO), which is now in managed code as opposed to a COM interface.


SQL Server "Yukon" now has a robust development environment that allows programmers to branch out into the world of data. Many of these people have only learned what they need to know in order to coexist with the database. SQL Server "Yukon" is blurring the line between being a developer and a database person. For me, it is the best things since sliced bread, because it combines the two technical joys of my life — development and database work.

For more information, look for my book, Introducing SQL Server "Yukon" Programming by Microsoft Press, due out second quarter 2004.

About the Author

Peter W. DeBetta is a Wintellect trainer and author who consults and develops enterprise-level software solutions using Visual Basic, ASP, the .NET Framework, and SQL Server. Peter speaks at various conferences around the world and has coauthored several books, including SQL Server 7.0 Programming Unleashed (SAMS, 1999); he is currently working on a new book for Microsoft Press entitled Introduction to SQL Server Yukon Programming. Peter is a very amateur photographer, singer and guitar player. Peter just recently became a father for the first time and is very much enjoying life with his wife and new son.

# # #

Page 2 of 2

This article was originally published on October 28, 2003

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