SQL Server 2008 : Advanced Stored Procedure Programming and Optimization - Installing and Using .NET CLR Stored Procedures

Prior to SQL Server 2005, the only way to extend the functionality of SQL Server beyond what was available using the T-SQL language was to create extended stored procedures or COM components. The main problems with these types of extensions were that if not written carefully, they could have an adverse impact on the reliability and security of SQL Server. Besides extended stored procedures and COM components, in SQL Server, the only language the stored procedures could be written in was T-SQL. Unfortunately, T-SQL has a somewhat limited command set for things such as complex string comparison and manipulation and complex numeric computations.

In SQL Server 2005 and later, you can create stored procedures in any Microsoft .NET Framework programming language, such as Microsoft Visual Basic .NET or Microsoft Visual C#. Stored procedures written in the common language runtime (CLR) are much more secure and reliable than extended stored procedures or COM components.

Adding CLR Stored Procedures to a Database

If you’ve already created and compiled a CLR stored procedure, the next step is to install that CLR procedure in the database. The first step in this process is to copy the .NET assembly to a location that SQL Server can access and then load it into SQL Server by creating an assembly. The syntax for the CREATE ASSEMBLY command is as follows:

CREATE ASSEMBLY AssemblyName [AUTHORIZATION LoginName]
FROM  StringPathToAssemblyDll
[WITH PERMISSION_SET (SAFE | EXTERNAL_ACCESS | UNSAFE) ]

AssemblyName is the name of the assembly, and StringPathToAssemblyDll is the path to the DLL. The path can be a local path, but often this path is a network share.

The WITH clause is optional, and it defaults to SAFE. Marking an assembly with the SAFE permission set indicates that no external resources (for example, the Registry, web services, file I/O) will be accessed.

The CREATE ASSEMBLY command fails if it is marked as SAFE and assemblies such as System.IO are referenced, and anything causing a permission demand for executing similar operations results in an exception being thrown at runtime.

Marking an assembly with the EXTERNAL_ACCESS permission set tells SQL Server that it will be using resources such as networking, files, and so forth. Assemblies such as System.Web.Services (but not System.Web) may be referenced with this set. To create an EXTERNAL_ACCESS assembly, the creator must have EXTERN_ACCESS permission.

Marking an assembly with the UNSAFE permission set tells SQL Server that not only might external resources be used, but unmanaged code may be invoked from managed code. An UNSAFE assembly can potentially undermine the security of either SQL Server or the CLR. Only members of the sysadmin role can create UNSAFE assemblies.

After an assembly has been created, the next step is to associate the method within the assembly with a stored procedure. This is done with the CREATE PROCEDURE command, using the following syntax:

Code View: Scroll / Show All

CREATE PROCEDURE [ schema_name. ] procedure_name
    ( [ { @parameter_name [AS] [ schema_name.]scalar_datatype [ = default ] }
      [ ,...n ] ] )
 [ AS ] EXTERNAL NAME assembly_name.class_name.method_name


					  

After the CLR procedure has been created successfully, it can be used just like a T-SQL stored procedure. The following example shows how to manually deploy a CLR stored procedure:

CREATE ASSEMBLY pr_address_verify
FROM 'F:\assemblies\address_routines\address_procs.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE PROCEDURE pr_address_verify (@address1 nvarchar(100),
                                    @address2 nvarchar(100),
                                    @city varchar(50),
                                    @state char(2),
                                    @zip char(9))
AS
EXTERNAL NAME [SQLCLR].[Routines.StoredProcedures].[address_verify]
go

Note

The preceding examples show the manual steps of registering an assembly and creating the CLR function. If you are using Visual Studio’s Deploy feature, Visual Studio automatically issues the CREATE/ALTER ASSEMBLY and CREATE PROCEDURE commands.

T-SQL or CLR Stored Procedures?

One question that often comes up is whether it’s better to develop stored procedures in T-SQL or in the CLR. The best answer is that the method you choose really depends on the situation and what functionality the procedure needs to implement.

The general rule is that if the stored procedure will be performing data access or large set-oriented operations with little or no complex procedural logic, it’s better to create the procedure in T-SQL for best performance. The reason is that T-SQL works more closely with the data and doesn’t require multiple transitions between the CLR and the SQL OS.

However, most benchmarks have shown that the CLR performs better than T-SQL for procedures that require a high level of computation or text manipulation. The CLR offers much richer APIs that provide capabilities not available in T-SQL for operations such as text manipulation, cryptography, I/O operations, data formatting, and invocation of web services. For example, T-SQL provides only rudimentary string manipulation capabilities, whereas .NET supports capabilities such as regular expressions, which are much more powerful for pattern matching and replacement than the T-SQL replace() function.

In a nutshell, performance tests have shown that T-SQL generally performs better for standard CRUD (create, read, update, delete) operations, whereas CLR code performs better for complex math, string manipulation, and other tasks that go beyond data access.