Microsoft & .NET.NETWhat's New and Cool in SQL Server "Yukon"

What’s New and Cool in SQL Server “Yukon”

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

A Developer’s Tale

Sure, SQL Server has lots of features that make it easy to manage and administer. Sure, it’s a world-class relational database management system. Sure, Transact-SQL (T-SQL) is an easy-to-learn language. Yet despite these and other facts, many developers only have a cursory knowledge of programming in SQL Server. I am here to announce that all that will change with SQL Server “Yukon”.

After years in the making, Microsoft is going to begin a new era of database programmability with its introduction of SQL Server “Yukon”. The changes include enhancements to some familiar features, such as T-SQL and DTS, and some new additions, including new object models, .NET integration, and some new services, such as the SQL Service Broker and Reporting Services. We are going to touch on a variety of these enhancements and new features in order to give you a taste of what’s to come.

Transact-SQL

T-SQL introduces a variety of new and enhanced features. The majority of the changes affect the ways in which a developer can manipulate data, which is of course, the main reason for using T-SQL code in the first place. Without going into too much detail, I’d like to highlight some of the big changes to the language set.

T-SQL has several new data types, including native handling for XML data, which is covered in the following section. There are also three new data-related data types: Date, Time and UtcDateTime. These new types have better accuracy and range than their existing counterparts, DateTime and SmallDateTime. They are also special implementations of .NET user-defined data types, and thus they have several special properties and methods that allow you to get portions of the date or time and even convert it to a string using a user-specified format.

T-SQL now has the ability to pivot and unpivot data. No more dynamic T-SQL or overly complex SELECT statements — now you can use this new ability to create crosstabulated data results. These two SELECT statements will give the same results, but obviously, the second is a lot easier to write, especially if we are dealing with more than three pivoted output columns.

SELECT ProductID, ProductName,      SUM(CASE TheYear WHEN 1996 THEN Extended ELSE 0 END) AS [1996],     SUM(CASE TheYear WHEN 1997 THEN Extended ELSE 0 END) AS [1997],     SUM(CASE TheYear WHEN 1998 THEN Extended ELSE 0 END) AS [1998]FROM vwOrderDetailsGROUP BY ProductID, ProductNameSELECT * FROM vwOrderDetailsPIVOT (SUM(Extended) FOR TheYear IN ([1996], [1997], [1998])) AS PVT

Common Table Expressions are new to SQL Server “Yukon” as well. At first, they appear to be another means of creating derived-tables, and they do indeed possess many of the same qualities. But, more in-depth research shows that they also give us the ability to write recursive queries. So if we want to iterate up or down a hierarchal table structure, we can create a query that references itself and will recurse as needed (with a default maximum of 100 levels deep) or until we specify otherwise. The following example uses the Employee table in the Northwind database to demonstrate how to find all employees and their managers, and those managers’ managers, etc….

WITH EmpCTE (EmpID, EmpName, MgrID, Level)AS   (SELECT   E.EmployeeID, E.LastName, E.ReportsTo, 1   FROM Employees E   WHERE ReportsTo IS NULL   UNION ALL   SELECT   E.EmployeeID, E.LastName, E.ReportsTo, Level + 1   FROM Employees E       INNER JOIN EmpCTE ON EmpCTE.EmpID = E.ReportsTo   WHERE Level <= 5)SELECT  EmpID, EmpName, MgrID, LevelFROM EmpCTE 

T-SQL now also has an improved exception handling mechanism, using the TRY…CATCH mechanism seen in .NET languages. Although not nearly as robust, it does allow the developer to catch many problems and thus handle issues that were once only manageable in the data access layer of code.

There are many other new features not mentioned here because of the space limitations. Of course, there will be lots of places to find out more, including various online sources, magazines and books. All I can say is, in my humble opinion, you will be quite happy with the changes.

XML

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.

ADO.NET

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.

Summary

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.

# # #

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories