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!

Summary

That's a quick summary of my comments. I'll try to write something a little longer when I get more time. And the conference is great!


Related Articles

Handling SQL Server Errors (5 April 2010)

Microsoft Releases Updated SQL Server 2005 SP2 (6 March 2007)

SQL Server 2005 SP2 Available (19 February 2007)

SQL Server 2005 Service Pack 2 CTP is Available (8 November 2006)

SQL Server 2005 SP1 (19 April 2006)

SQL Server 2005 RTM Code Available (27 October 2005)

SQL Server 2005 Release Date - November 7th, 2005 (7 June 2005)

April CTP (aka Beta 3) Available Today (18 April 2005)

Other Recent Forum Posts

Performance tuning (14m)

As I gain experience and get older, I'm working much slower, but producing better quality, but (3h)

Master DB 2019 problem (18h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (1d)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

- Advertisement -