Introducing SQL Server 2005's CLR Integration
( Page 1 of 2 )

SQL Server 2005 will integrate with the CLR, so you can deploy C# or VB.NET code within the SQL Server process. If you need complex procedural code, you can write it as managed code. In this first of a two-part series, Shawn Wildermuth explains what's inv

T-SQL is great for database code, but writing procedural code in T-SQL has always been difficult. Invariably, your project includes a stored procedure or two, requiring some text parsing or complex math operations. Doing this in SQL Server has been difficult. Either you wrote T-SQL code, or you wrote an extended stored procedure/function and used COM to interoperate them. Neither was a good solution, but that is all we had.

In comes SQL Server 2005 with its CLR integration to alleviate these problems. By integrating the CLR, SQL Server 2005 allows you to deploy C# or VB.NET code that is used within the SQL Server process. This means that if you need complex procedural code, you can write it as managed code.

Integrating the CLR into SQL Server is not a step to eliminating T-SQL. As .NET developers, it may seem like a good idea to do all your database code in managed code, but this is not the case. Think of the CLR integration as just another tool in your toolbox. This is the hammer that, I suspect, will be used to hammer in nails, screws, and 2x4s in projects the next year. It will be overused. Don't let your project be the ones caught guilty of this.

Details of the CLR Integration

Integrating the CLR into SQL Server involves a number of different features. Many of those features allow developers to do things they have never had the opportunity to do before in SQL Server. But, before I discuss the features and how they work, it's important to consider some details on how the CLR is hosted in SQL Server.

Integrating the CLR into the SQL Server 2005 engine was not done in a trivial way. At the end of the day, SQL Server 2005 has to be a rock-solid implementation of the database. Any new feature has had to endure intense scrutiny for how it will impact the stability of SQL Server.

In the 1.x version of the CLR, the chief client for hosting the CLR was Internet Information Server (IIS). IIS is a peculiar beast. If it finds badly behaving code, it is happy to kill threads and processes and just restart the code. Any code living within IIS was free to allocate memory, threads or even new processes, as it saw fit. Unfortunately, in SQL Server this is the opposite of the needed requirements. If some piece of CLR code starts to act in a bad way, destroying the SQL Server process is the completely wrong thing to do. The health of the SQL Server processes are critical to the stability of the platform.

The 2.0 version of the hosting environment has many more ways to communicating with the host environment. These new communication mechanisms allow for SQL Server to be in control of key operations of the CLR. SQL Server may refuse to allow creation of new memory or new threads, and disallow the destruction of the host process. In addition, the CLR integration puts CLR code into a secure sandbox of operations to improve on the stability and security inside SQL Server.

Using Managed Code

Now that you have an assembly or two loaded, you want to know how to actually have code run within SQL Server. Within SQL Server, most types of code blocks that you are familiar with in T-SQL are supported in managed code:

• Stored Procedures
• Functions
• Triggers

A new type of code is supported in SQL Server 2005 called a custom aggregation. This allows you to write code that supports aggregating data. You can do things like create a custom SUM or COUNT aggregation. You might create useful extensions to SQL Server, like Standard Deviations. I'll cover custom aggregations in more specifics below.

Using managed code within SQL Server 2005 requires three steps:

1. You must write the managed code and compile it into an assembly.
2. You must install the managed code's assemblies into SQL Server 2005.
3. You must use DDL statements to tie the managed code to named objects (Stored Procedures, functions, etc.)

I'll explain each of these steps.

>>> More Using VS Articles          >>> More By Shawn Wildermuth