Chapter 4. Introduction to Common Language Runtime (CLR) Integration

The .NET Framework Common Language Runtime (CLR) is an environment that executes compiled code written in programming languages such as C# and VB.NET. The code is compiled to a file called an assembly that contains the compiled code together with an assembly manifest. The manifest contains metadata about the assembly, including types, methods, and inheritance relationships. Code running within the CLR is called managed code.

The CLR provides services such as automatic garbage collection, security support, and runtime type checking. Because the compiled code is executed by the CLR rather than directly by the operating system, managed code applications are platform- and language-independent.

SQL Server 2005 hosts the CLR in the Database Engine. This is called CLR integration. CLR integration lets you create database objects such as functions, stored procedures, triggers, user-defined types (UDTs), and user-defined aggregate (UDA) functions in programming languages supported by the CLR. Managed code running in SQL Server-hosted CLR is referred to as a CLR routine.

Prior to SQL Server 2005, the main way that SQL Server was extended was using extended stored procedures which let you create external routines using programming languages such as C. Extended stored procedures are used like regular stored procedures, however can have performance problems such as memory leaks and can cause the server to become unreliable. CLR integration lets you extend SQL Server with the safety and reliability of T-SQL and with the flexibility of extended stored procedures.

Managed code uses code access security (CAS) to control what operations assemblies can perform. CAS secures the code running within SQL Server and prevents the code from adversely affecting the operating system or the database server.

Generally, you should use T-SQL when the code in the routines primarily performs data access. CLR routines are best for CPU-intensive calculations and for supporting complex logic that would otherwise be difficult to implement using T-SQL.

The components needed to develop CLR routines are installed with SQL Server 2005. Although SQL Server 2005 ships with the .NET Framework and command-line compilers for C# and VB.NET, as well as a Visual Studio .NET IDE that lets you build Analysis Services and Reporting Services projects, you need to install Visual Studio 2005 to create CLR routines in Visual Studio 2005.