Understanding CLR and SQL Server 2005 Database Engine

The integration of the CLR with SQL Server extends the capability of SQL Server in several important ways. While T-SQL, the existing data access and manipulation language, is well suited for set-oriented data access operations, it also has limitations. Designed more than a decade ago, T-SQL is a procedural language, not an object-oriented language. The integration of the CLR with SQL Server 2005 brings with it the ability to create database objects using modern object-oriented languages like VB.NET and C#. While these languages do not have the same strong set-oriented nature as T-SQL, they do support complex logic, have better computation capabilities, provide access to external resources, facilitate code reuse, and have a first-class development environment that provides much more power than the old Query Analyzer.

The integration of the .NET CLR with SQL Server 2005 enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages. The integration of the .NET CLR with SQL Server 2005 is more than just skin deep. In fact, the SQL Server 2005 database engine hosts the CLR in-process. Using a set of APIs, the SQL Server engine performs all of the memory management for hosted CLR programs.

The managed code accesses the database using ADO.NET in conjunction with the new SQL Server .NET Data Provider. A new SQL Server object called an assembly is the unit of deployment for .NET objects with the database. To create CLR database objects, you must first create a DLL using Visual Studio 2005. Then you import that DLL into SQL Server as an assembly. Finally, you link that assembly to a database object such as a stored procedure or a trigger. In the next section you’ll get a more detailed look at how you actually use the new CLR features found in SQL Server 2005.

CLR Architecture

The .NET Framework CLR is very tightly integrated with the SQL Server 2005 database engine. In fact, the SQL Server database engine hosts the CLR. This tight level of integration gives SQL Server 2005 several distinct advantages over the .NET integration that’s provided by DB2 and Oracle.

A SQL Server database uses a special API or hosting layer to communicate with the CLR and interface the CLR with the Windows operating system. Hosting the CLR within the SQL Server database gives the SQL Server database engine the ability to control several important aspects of the CLR, including

Memory management

Threading

Garbage collection

The DB2 and Oracle implementation both use the CLR as an external process, which means that the CLR and the database engine both compete for system resources. SQL Server 2005’s in-process hosting of the CLR provides several important advantages over the external implementation used by Oracle or DB2. First, in-process hosting enables SQL Server to control the execution of the CLR, putting essential functions such as memory management, garbage collection, and threading under the control of the SQL Server database engine. In an external implementation the CLR will manage these things independently. The database engine has a better view of the system requirements as a whole and can manage memory and threads better than the CLR can do on its own. In the end, hosting the CLR in-process will provide better performance and scalability.

Enabling CLR Support

By default, the CLR support in the SQL Server database engine is turned off. This ensures that update installations of SQL Server do not unintentionally introduce new functionality without the explicit involvement of the administrator. To enable SQL Server’s CLR support, you need to use the advanced options of SQL Server’s sp_configure system stored procedure, as shown in the following listing:

sp_configure  'show advanced options', 1
   GO
   RECONFIGURE
   GO
   sp_configure  'clr enabled', 1
   GO
   RECONFIGURE
   GO

CLR Database Object Components

To create .NET database objects, you start by writing managed code in any one of the .NET languages, such as VB, C#, or Managed C++, and compile it into a .NET DLL (dynamic link library). The most common way to do this would be to use Visual Studio 2005 to create a new SQL Server project and then build that project, which creates the DLL. Alternatively, you create the .NET code using your editor of choice and then compiling the code into a .NET DLL using the .NET Framework SDK. ADO.NET is the middleware that connects the CLR DLL to the SQL Server database. Once the .NET DLL has been created, you need to register that DLL with SQL Server, creating a new SQL Server database object called an assembly. The assembly essentially encapsulates the .NET DLL. You then create a new database object such as a stored procedure or a trigger that points to the SQL Server assembly.

SQL Server .NET Data Provider

If you’re familiar with ADO.NET, you may wonder exactly how CLR database objects connect to the database. After all, ADO.NET makes its database connection using client-based .NET data providers such as the .NET Framework Data Provider for SQL Server, which connects using networked libraries. While that’s great for a client application, going through the system’s networking support for a database call isn’t the most efficient mode for code that’s running directly on the server. To address this issue, Microsoft created the new SQL Server .NET Data Provider. The SQL Server .NET Data Provider establishes an in-memory connection to the SQL Server database.

Assemblies

After the coding for the CLR object has been completed, you can use that code to create a SQL Server assembly. If you’re using Visual Studio 2005, then you can simply select the Deploy option, which will take care of both creating the SQL Server assembly as well as creating the target database object. If you’re not using Visual Studio 2005 or you want to perform the deployment process manually, then you need to copy the .NET DLL to a common storage location of your choice. Then, using SQL Server Management Studio, you can execute a T-SQL CREATE ASSEMBLY statement that references the location of the .NET DLL, as you can see in the following listing:

CREATE  ASSEMBLY MyCLRDLL
   FROM  '\\SERVERNAME\CodeLibrary\MyCLRDLL.dll'
   Code database object  using
   managed code and  complie to DLL
   Register DLL with SOL  Server using

The CREATE ASSEMBLY command takes a parameter that contains the path to the DLL that will be loaded into SQL Server. This can be a local path, but more often it will be a path to a networked file share. When the CREATE ASSEMBLY is executed, the DLL is copied into the master database. If an assembly is updated or becomes deprecated, then you can remove the assembly using the DROP ASSEMBLY command as follows:

DROP  ASSEMBLY MyCLRDLL

Because assemblies are stored in the database, when the source code for that assembly is modified and the assembly is recompiled, the assembly must first be dropped from the database using the DROP ASSEMBLY command and then reloaded using the CREATE ASSEMBLY command before the updates will be reflected in the SQL Server database objects.

You can use the sys.assemblies view to view the assemblies that have been added to SQL Server 2005 as shown here:

SELECT *  FROM sys.assemblies

Since assemblies are created using external files, you may also want to view the files that were used to create those assemblies. You can do that using the sys. assembly_files view as shown here:

SELECT *  FROM sys.assembly_files

Creating CLR Database Objects

After the SQL Server assembly is created, you can then use SQL Server Management Studio to execute a T-SQL CREATE PROCEDURE, CREATE TRIGGER, CREATE FUNCTION, CREATE TYPE, or CREATE AGGREGATE statement that uses the EXTERNAL NAME clause to point to the assembly that you created earlier.

When the assembly is created, the DLL is copied into the target SQL Server database and the assembly is registered. The following code illustrates creating the MyCLRProc stored procedure that uses the MyCLRDLL assembly:

CREATE  PROCEDURE MyCLRProc
   AS  EXTERNAL NAME
   MyCLRDLL.StoredProcedures.MyCLRProc

The EXTERNAL NAME clause is new to SQL Server 2005. Here the EXTERNAL NAME clause specifies that the stored procedure MyCLRProc will be created using a .SQL Server assembly. The DLL that is encapsulated in the SQL Server assembly can contain multiple classes and methods; the EXTERNAL NAME statement uses the following syntax to identify the correct class and method to use from the assembly:

Assembly  Name.ClassName.MethodName

In the case of the preceding example, the registered assembly is named MyCLRDLL. The class within the assembly is StoredProcedures, and the method within that class that will be executed is MyCLRProc.