Yukon Engine: CLR Integration I

I thought it might be of interest to some to put a few notes up from the talk
I gave on Yukon development
last week. If nothing else, it’s helpful to me to
have as a reminder for the next time I give this talk! I’ll split this into several
parts for ease of access, and post them over the course of the week.

In the current release of SQL Server, there are effectively three ways to embed custom
logic into your database:

  1. Writing stored
    , user-defined
    and triggers using
  2. Creating
    extended stored procedures
    using a C-style DLL written to a special API;
  3. Building
    your logic into an external component
    (perhaps .NET or COM) and ensuring that
    all database manipulation occurs via your own component.

Traditionally developers have opted for a mixture of options 1 and 3. Option
1 is perfect from the point of view of a database purist – everything that impacts
the integrity of the database can be kept there; nothing can bypass the code, as it’s
embedded in the database. However, whilst T-SQL is great for set-based data manipulation,
it doesn’t have the same degree of structure and elegance as most component-orientated
languages, offers limited support for string handling and many other constructs, and
doesn’t perform awfully well when you’re not dealing directly with the database.

Option 3 allows you to build access logic that abstracts data from a relational form
into more business-centric idioms such as customer, purchase order etc., as well as
offloading this work from the database server to other application servers. But because
the data tier is separate from the database itself, it’s not always easy to integrate
any semantic validation from here into other data services such as replication or

Most people steer away from option 2 (extended stored procedures), because they’re
quite fragile due to their in-process nature – a memory leak in one of these can bring
the whole database server down, for instance. They’re also rather awkward to write.
The guarded
wording used in this security note
is certainly enough to scare me off recommending
them, at any rate.

The big change in SQL Server “Yukon” is that the Common Language Runtime (CLR) is
hosted directly in the database engine, allowing .NET managed code to be used for
stored procedures, functions or triggers. On the surface, this is the best of all
worlds – your code resides in the database, but can take advantage of the full richness
of the .NET Framework Base Class Libraries. It performs better than T-SQL for computationally-intensive
tasks, can be secured using both the SQL security architecture and .NET Code Access
Security, and allows you to use any .NET language to develop everything from the front-end
user interface to the back-end database logic itself.

Here’s a few examples of how you might use this capability to build a richer database:

  • Utilise the .NET cryptography classes to encrypt sensitive data in the database for
    added security;
  • Use regular expressions in a trigger to validate structured string data such as email
    addresses, phone numbers and postal codes prior to insertion in the database;
  • Create table-valued functions that combine data from a SQL table with data from an
    external source or other values derived through computation and lookups based on the
    existing data.

On top of all this, you can also create custom user-defined data types and aggregates
using the .NET integration; for example, you could have a custom type that represents
a financial instrument and then use a custom aggregation to ensure that it was rolled
up appropriately for reports.

One thing that struck me in particular about the CLR integration was how Yukon took
advantage of the extended CLR hosting APIs in Whidbey. Traditionally the CLR takes
care of memory management, garbage collection and thread support itself, but when
it runs in Yukon these services are delegated to the database engine. The engine has
a much better understanding of the current system load as a whole and can therefore
manage memory and threads appropriately for the entire execution environment. This
results in a more robust and scalable solution than if these services were left within
the CLR itself.

In part two, I’ll talk more about how you take advantage of these extensibility points
using Visual Studio “Whidbey”.

Comments (5)

  1. Anonymous says:

    Whilst this certainly expands your capabilities within SPs i presume the option is still there to go with a purely old-school T-SQL SP? Surely it cannot be possible to write managed code to join two tables with anywhere near the same speed as that achieved by SQL Server compiled SP?

    And of course the danger of tempting dev’s to put thier code SQL-side lies in thier code munching up the CPU on a centralised resource rather than moving it out to a farm of app servers….

  2. Anonymous says:

    You’re absolutely right: you can continue to write stored procedures, functions and triggers in T-SQL; indeed, I’ll be exploring the pros/cons of each approach as part of the next exciting installment 🙂

  3. Anonymous says:

    I’d be interested in hearing more about the thread management story. In particular, when running Sql Server in fiber-mode and how that affects managed threads.

  4. Anonymous says:

    Hi Tim
    Are you scheduled to repeat that talk again in the s.e in the near future? I’d like to hear you give it.


  5. Anonymous says:

    John, that’s a great question. I don’t have all the answers myself, but I’ll ask around and see what I can find out for you.

    Russ, I’ll be delivering a very similar talk at the SQL Server User Group conference in Reading on the 27th Jan; more details at http://www.sqlserverfaq.com. I’ll also be delivering it as a webcast at some point within the next six weeks; I’ll post details on the blog when this is finalised.