Yukon Demo at PASS

By Bill Graziano on 22 November 2002 | Tags: SQL Server 2005

I've spent the last three days at the PASS conference and thought I'd pass along some of the information on Yukon. Unlike my earlier wild guesses this stuff has a much better chance of being true. This article is mostly just what I saw with little commentary. I'll write something up later with some more detailed thoughts.

The first thing they demonstrated was an OLAP query debugger. Now I'm not a big OLAP user but the people that use OLAP regularly were pretty impressed.

CLR Integration

It's already well known that the Common Language Runtime (CLR) languages (C#, VB.NET, etc.) will run in this release of SQL Server since Microsoft is including the CLR. The CLR will use the SQL Server threading and memory management routines rather than being a seperate bolt-on. That means that the CLR languages will run inside the SQL Server process as T-SQL commands do now.

You'll be able to use the CLR laguages for stored procedures, triggers and user-defined funtions. In C#, you'll write code that looks nearly identical to C# today. You can query a database, return a recordset and process it all inside the SQL Server process.

The first step is to write a class that exposes certain methods then compile this to a DLL. Then create an assembly using the CREATE ASSEMBLY statement that points to the DLL. You are also required to specify security requirements that can restrict what the code in this assembly can do. For example, you could prevent a DLL from accessing anything outside SQL Server.

The next step is to create an extended stored procedure that references the method and define the parameters it requires. At that point all you need to do is call the extended stored procedure like you would any other stored procedure.

It will be interesting to see how this is used. While C# is certainly a much more powerfull language than T-SQL, that's a lot of steps to use a C# method inside SQL Server. I'm sure the process will be more automated when the product ships. I'm guessing vendors will probably see this as a great way to obscure their code. They can ship the DLL's and register them without having to provide source code for their stored procedures.

You can also create user-defined aggregates in the CLR languages. SQL Server ships with some aggregates such as SUM and COUNT. The example they showed "summed" an XML column. In their custom aggregate they were parsing the XML document for a particular attribute and summing just that value.

CLR integration started out being the feature I was most excited about in SQL Server but it's gradually moving down the list as I learn about other things that are planned. I think it's one of those things that I won't appreciate until I really, really need it. I might also mention that my previous article was completely wrong about how C# would be written inside SQL Server. There was even a comment posted on the article that basically described the approach I saw today.

XML

The XML stuff is simply amazing. They are adding an XML datatype. You can define a table like this:

CREATE TABLE MyTable 
(InvoiceNumber int not null,
 InvoiceBody xml)

The XML datatype can be used in columns, parameters and variables. And you can create indexes on information inside the XML column. Cool stuff!

Miscellaneous

They're adding some new features to T-SQL also. The main one I saw was exception handling. They described it as a "try catch" block but we didn't get to see a demo. That will be very, very handy.

There are a few other things that are also being added such as a date datatype and a time datatype. They also had something described as a varchar(max) datatype. I wasn't sure if that meant a varchar column with no upper bound (i.e a text column without the limiations) or the ability to define the varchar column with a very large size (i.e. varchar(50000) ). I'd prefer a varchar of unlimited size myself. In either case it sounds great. I'm sick of dealing with the limitations of text datatypes.

Yukon will also support recursive queries using the ANSI standard. I searched for a standard sample query but couldn't find anything. This seems to mean that parent-child relationships to any level of depth can be returned using a single SELECT statement.

They also described something called query notification. Basically you can "register" a SELECT statement and if anything changes in the SELECT statement you'll be notified. No idea how they'll do this but if they can pull it off and not hurt performance it will be great!