Implement User-defined Functions in SQL Server 2005 with Managed Code, Page 2
Manual Registration and Deployment of Assembly
To utilize the manual registration and deployment of assembly approach, you need to go through the following steps:
- Create the managed class that implements the functionalities of the user-defined function.
- Compile that class using the .NET language compiler to produce an assembly.
- Register the assembly with SQL Server using the Create Assembly statement.
- 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:
Now that you have seen the manual approach, see how Visual Studio can aid you in eliminating this manual step.