ADO.NET 3.5 Cookbook, 2nd Edition

Chapter 12. SQL Server CLR Integration

12.0. Introduction

The .NET Framework Common Language Runtime (CLR) environment executes compiled code written in programming languages such as C# and Visual Basic. The code is compiled to a file, or assembly, that contains the compiled code and 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 and later versions host 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 that let you create external routines using programming languages such as C. Extended stored procedures are used like regular stored procedures, however, they can have performance problems such as memory leaks and can cause the server to become unreliable. CLR integration ...