Implement User-Defined Functions in SQL Server 2005 with Managed Code

Introduction

Although Transact-SQL (T-SQL), the existing data-access and manipulation language, is well suited for set-oriented, data-access operations, it also has limitations. Microsoft designed it more than a decade ago, and it is a procedural language rather than an object-oriented language. The integration of the .NET CLR with SQL Server enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages. The SQL Server engine hosts the CLR in process. All managed code that executes in the server runs within the confines of the CLR. The managed code accesses the database using ADO.NET and the new SQL Server .NET Data Provider.

User-defined functions that return scalar types must return a .NET data type that can be implicitly converted to a SQL Server data type. Scalar functions written with the .NET Framework can significantly outperform T-SQL in certain scenarios because, unlike T-SQL, .NET functions are created using compiled code. User-defined functions can also return table types, in which case the function must return a result set.

Before learning to create user-defined functions, read the following section to gain an understanding of the advantages that CLR integration with SQL Server offers.

Advantages of CLR Integration

In previous versions of SQL Server, database programmers were limited to using T-SQL when writing code on the server side. With CLR integration, database developers can now perform tasks that were impossible or difficult to achieve with T-SQL alone. Both Visual Basic .NET and C# offer full support for arrays, structured exception handling, and collections. With these languages, developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks. Visual Basic .NET and C# offer object-oriented capabilities such as encapsulation, inheritance, and polymorphism. Related code can now be easily organized into classes and namespaces.

Managed code is better suited than T-SQL for number crunching and complicated execution logic, and it features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Base Class Library (BCL), database developers now have access to thousands of pre-built classes and routines, which can be accessed easily from any stored procedure, trigger, or user-defined function. The BCL includes classes that provide functionality for improved string functioning, advanced math operations, file access, cryptography, and more. While many of these classes are available for use from within SQL CLR code, those that are not appropriate for server-side use (for example, windowing classes) are not available.

One of the benefits of managed code is type safety. Before managed code is executed, the CLR performs several checks to ensure that the code is safe to run. This process is known as verification. For example, it checks the code to ensure that memory that has not be been written to is not read. The CLR also prevents buffer overflows. By default, both Visual Basic .NET and C# always produce safe code. However, C# programmers have the option of using the unsafe keyword to produce unsafe code that, for example, directly accesses memory.

What Is an Assembly?

An assembly is the unit of deployment for .NET objects with the database. To create objects, you must write managed code that implements the functionalities of the user-defined function. Once the code is written, you compile it into a .NET assembly and then deploy it onto SQL Server. You can accomplish this in one of the following two ways:

  • The most common and simple way is by using Visual Studio 2005 to create a new SQL Server Project. After creating the project, you then can very easily create the user-defined function and deploy it onto SQL Server with the click of a button, thereby eliminating the manual registration step.
  • Create a Visual Studio Class library project and compile the class library into an assembly. Once the assembly is created, you then can register the assembly with SQL Server and then associate the definition of the user-defined function with a specific method in the assembly.

The second approach is a manual step and the first approach is an automated process that eliminates most of the steps. The following sections look at each of these methods.

Manual Registration and Deployment of Assembly

To utilize the manual registration and deployment of assembly approach, you need to go through the following steps:

  1. Create the managed class that implements the functionalities of the user-defined function.
  2. Compile that class using the .NET language compiler to produce an assembly.
  3. Register the assembly with SQL Server using the Create Assembly statement.
  4. Associate the definition of the user-defined function with the specific method in the class.

After you create the assembly, you then can load it into SQL Server using the T-SQL CREATE ASSEMBLY command:

CREATE ASSEMBLY MyAssembly FROM 'C:\Temp\MyAssembly.dll'

The CREATE ASSEMBLY command takes a parameter that contains the path to the assembly you will load into SQL Server. This can be a local path, but more often it is a path to a networked file share. When the CREATE ASSEMBLY command is executed, the assembly is copied into the master database.

When loading an assembly into SQL Server, you also have the ability to specify one of three different levels of security in which your code can run:

  • SAFE—SAFE is the default permission set and works for the majority of scenarios. When code in an assembly runs under the SAFE permission set, it can do computation and data access only within the server via the in-process managed provider.
  • EXTERNAL_ACCESS—This is a code permission set that addresses scenarios where the code needs to access resources outside the server (such as the files, network, Registry, and environment variables). Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code. To create an EXTERNAL ACCESS assembly, the creator needs to have EXTERNAL ACCESS permission.
  • UNSAFE—Assemblies can call into unmanaged code, and they are not verified to be type-safe.

To specify a given security level, you modify the syntax of the CREATE ASSEMBLY statement:

CREATE ASSEMBLY <AssemblyName>
FROM '<Path>'
WITH PERMISSION_SET = <Permission_Set>

For example, to specify that an assembly should run with EXTERNAL_ACCESS permissions, you simply change the permission set that is specified when loading the assembly:

CREATE ASSEMBLY MyAssembly
FROM 'C:\Test\MyAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

After registering the assembly, you also can remove the assembly by using the DROP ASSEMBLY command:

DROP ASSEMBLY MyAssembly

Because an assembly is persisted in the database when the source code for that assembly changes and the assembly is recompiled, you first must drop the assembly from the database (using the DROP ASSEMBLY command) and then re-add it (using the CREATE ASSEMBLY command) before the updates will be reflected in SQL Server database objects.

So far, you have completed the creation, compilation, and registration steps. Now, you need to associate the SQL Server function definition to the appropriate method in the previously registered assembly. The following example illustrates how you can use the CREATE FUNCTION statement to create a .NET user-defined function:

CREATE FUNCTION MyFunction()
RETURNS INT AS
EXTERNAL NAME
MyAssembly:[MyClass]::MyFunction

For user-defined functions, the CREATE FUNCTION statement has been extended with the EXTERNAL NAME clause, which essentially links the user-defined function name to the appropriate method in the .NET assembly. In this example, the MyFunction method uses the assembly named MyAssembly. Within that assembly, it’s using the MyFunction method inside the class named MyClass. The syntax of the External name is as follows:

Assembly Name:[AssemblyNamespace.TypeName]::MethodName

Now that you have seen the manual approach, see how Visual Studio can aid you in eliminating this manual step.

Implementing User-Defined Functions Using Visual Studio 2005

So far, you have seen the steps involved in creating and deploying a SQL Server user-defined function using the manual steps. This section shows you how to use Visual Studio 2005 to automate the compilation and deployment of the user-defined functions.

To start, create a new SQL Server Project using the File->New Project menu in Visual Studio 2005 and specify the project name as UDFExamples. (See the following screenshot.)

Because you are creating a database project, you need to associate a data source with the project. To this end, Visual Studio automatically prompts you to either select an existing database reference or add a new database reference. For the purposes of this example, use a database called Test. (See the following screenshot.)

Once you’ve created the project, select Project->Add User-Defined Function from the menu. In the Add New Item dialog box, enter Add.cs and click the Add button. (See the following screenshot.)

After you’ve created the class, modify the code in the class to look like the following:

using System;
using System.Data.Sql;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
   [SqlFunction]
   public static SqlInt32 Add(int x, int y)
   {
      return x + y;
   }
};

The above code starts by importing the required namespaces. It then declares a partial class named UserDefinedFunctions. (The partial class is a new feature in .NET Framework 2.0. It gives you the ability to split a single class into more than one source code file. This feature is very helpful when more than one user works on the same class. Without this concept, you would have to use source code management features such as check-in/check-out and merge changes for multiple programmers to work on a single class. With partial types, each team member can work on a separate part of the class, and the system will handle merging the separate code files back into a single class at compile time.)

After that, the code declares a static method named Add that takes in two parameters. The Add method is decorated with the SqlFunction attribute, which specifies that the Add method be exposed as a user-defined function to the consumers. Inside the Add method, it simply returns the result of the addition of the two numbers to the caller.

Now that the user-defined function is created, deploying it is very simple and straightforward. Before deploying it, you need to build the project. Select Build->Build UDFExamples from the menu, which compiles all the classes in the project. Any compilation errors will be displayed in the Error List pane.

Once the project is built, you then can deploy it on SQL Server by selecting Build->Deploy UDFExamples from the menu. This will not only register the assembly but also deploy the user-defined function in SQL Server.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read