Last week, I delivered a session at TechEd Israel on the Yukon CLR.

Basically, SQL Server 2005 will host the CLR which enables developers to:

  1. Write procedural code (stored procedures, user defined functions, triggers) using any mananged language for which the compiler generates verifyable IL.
  2. Extend SQL Server with User Defined Aggregates - you know, write your own min, max, avg - or user defined data types.

Except for the user defined aggregates, I covered all of these and showed a simple demo. You can find slides and demo here: http://iw.microsoftwss.be/EYT/HansVBBlogDrops . Look for Dev402 content.

Especially creating the user defined data type (demo 5) took me some time because I overlooked some simple details:

  1.  My first suggestion is to use Visual Studio to create a UDT. You get a nice template wich alrady implements the Inullable interface.
  2. Then add work on the ToString method and the Parse method but before you test whether this works, you should make sure that the IsNull property of the Inullable interface doesn't always return Null! Apparently this property is checked before the ToString method is called. If your IsNull property always returns null, you will never see the state of your stored instances.
  3. I have a method in method in my UDT called contains number which returns a SqlBoolean. Using this method in your select statements caused me some trouble. Was it because it was getting really late? I tried many things:
  • Declare @value int
    set @value = 42
    Select a::ToString() from demo5
    WHERE a::ContainsNumber(@value) 
  • Declare @value int
    set @value = 42
    Select a::ToString() from demo5
    WHERE a::ContainsNumber(@value) = True
  • Declare @value int
    set @value = 42
    Select a::ToString() from demo5
    WHERE a::ContainsNumber(@value) = 1

Easy to say afterwards but only the last one worked. I can see why the second doesn't work. But I really thought the first one would have worked as well. Thinking about it a bit earlier on the day, maybe the following would have worked as well. I'm not going to fire up my VPC now, I have other work to do but I'll change this blog entry once I find out.

  • Declare @value int
    set @value = 42
    Select a::ToString() from demo5
    WHERE exists(a::ContainsNumber(@value))

I'm really looking forward to Beta 2 when the product will be more complete and when there will be a much broader availability of the bits. I'm sure we will get a hot Yukon summer...

This posting is provided "AS IS" with no warranties, and confers no rights.