Introduction

One of the major new features of SQL Server "Yukon" is the integration of the .NET Framework Common Language Runtime (CLR) into the SQL Server database engine. This means that you now have the possibility to write your stored procedures, triggers and user-defined functions in managed code. The languages currently supported are C#, Visual Basic .NET, managed C++ and JavaScript .NET. Furthermore, SQL Server "Yukon" can only host the "Whidbey" version of the .NET Framework. Previous versions of the .NET Framework are not supported. For performance reasons, the .NET runtime is lazy loaded by SQL Server. This means that the .NET runtime will only be loaded when it is really necessary, such as when you would execute a managed stored procedure for the first time.

Whether or not you should implement your stored procedures and user-defined functions using .NET code depends largely on what you do in those functions. If they contain a lot of procedural code then writing them in .NET will typically make them faster and easier to implement. If, on the other hand, you do a lot of data access in your functions, implementing them in T-SQL will typically yield the best performance.

Starting from a .NET Assembly

To start we will create a new .NET Class Library in Visual Studio "Whidbey". This Class Library project is called MathTutor and will implement some simple mathematical functions.

Not all functions that you implement in .NET classes can be accessed by T-SQL. The methods you want to expose as stored procedures, triggers or user-defined functions must follow at least 3 conditions:

• They must be in public classes.
• They must be static and public.
• They cannot be in nested classes.

In the MathTutor project we will implement one class called Math with 3 simple functions. The initial version of this class is shown below.

namespace MathTutor
{
     public class Math
     {
          public static SqlInt32 AddNumbers(SqlInt32 i, SqlInt32 j)
          {
               return i + j;
          }
 
          public static int SubtractNumbers(int i, int j)
          {
               return i - j;
          }

          public static SqlInt32 IncrementBy(SqlInt32 by, ref SqlInt32 number)
          {
               int retValue = 0;
 
               try
               {
                    number += by;
               }
               catch (Exception ex)
               {
                    retValue = 1;
               }
               return retValue;
          }
     }
} 

Note that to declare the parameters of the methods, you can either use the standard types provided by the .NET Framework or their corresponding SqlTypes. If you know that your methods will only be used in T-SQL it is preferable to use the SqlTypes which are defined in the System.Data.SqlTypes namespace. The SqlTypes behave in the same way as the built-in SQL Server data types, especially when you're working with NULL values.

Registering Class Libraries in SQL Server

Once the Class Library is compiled we need to perform two steps in SQL Server "Yukon" to expose the methods in our MathTutor class library:

• Register the assembly in SQL Server "Yukon".
• Register the methods in the assembly as stored procedures, triggers or user-defined functions.

These steps are mandatory because SQL Server "Yukon" cannot execute any arbitrary managed code that you might have in your assemblies.

The first step is accomplished using the CREATE ASSEMBLY statement as shown below.

CREATE ASSEMBLY MathTutor
FROM 'C:\Articles\Yukon\MathTutor\bin\Debug\MathTutor.dll'

The CREATE ASSEMBLY statement registers and loads an assembly in SQL Server. In the FROM clause you specify the location of the assembly you want to register. If this assembly depends on other assemblies you will also need to register these. Once the assembly is registered the original assemblies on the file system are not used anymore. All the registration information is stored in 3 SQL Server system tables. These are called sys.assemblies, sys.assembly_files and sys.assembly_modules.

When you load an assembly in SQL Server you have the possibility to specify a security level for you managed code. This is done using the WITH PERMISSION_SET parameter as shown below.

CREATE ASSEMBLY MathTutor
FROM 'C:\Articles\Yukon\MathTutor\bin\Debug\MathTutor.dll'
WITH PERMISSION_SET = SAFE

When the permission set is set to safe, which is the default, the managed code in our assembly cannot access any external resources like the file system, registry, network...

To un-register an assembly you can use the DROP ASSEMBLY statement.

DROP ASSEMBLY MathTutor

Registering User-Defined Functions in SQL Server

Once the assembly is registered, AddNumbers and SubtractNumbers can be registered as user-defined functions in T-SQL. This is done using the CREATE FUNCTION statement.

CREATE FUNCTION AddNumbers (@I INT, @J INT) RETURNS INT
AS EXTERNAL NAME MathTutor:[MathTutor.Math]::AddNumbers

CREATE FUNCTION SubtractNumbers (@I INT, @J INT) RETURNS INT
AS EXTERNAL NAME MathTutor:[MathTutor.Math]::SubtractNumbers

In the CREATE FUNCTION statement, the clause EXTERNAL NAME is used to indicate that the user-defined function maps to a method in the assembly. The name of the method you want to register is written as AssemblyName:FullyQualifiedClassName::MethodName.

Once the user-defined functions are registered we can call them using the same syntax as you would use for T-SQL user-defined functions.

If you execute the following statement

SELECT dbo.AddNumbers(10,20)

you will see the result shown below:

-----------
30

(1 row(s) affected)

If your user-defined functions do not execute as expected you can debug them by attaching the Visual Studio .NET debugger to the SQL Server process. This process is called SqlServr.exe as show in the picture below.

Figure 1: Attaching the Visual Studio .NET debugger to the SQL Server process

Once the debugger is attached to the SQL Server process, execute your user-defined function in the SQL Workbench. The breakpoint in your Visual Studio .NET project will be hit and you can debug your source code.

As explained previously, you can either use standard types or SqlTypes for the declaration of the parameters or return value of your user-defined functions. The main difference between them lies in the way NULL values are handled. If you pass a NULL value to a standard type parameter an exception is generated. This is due to the fact that standard types like Int32 are not nullable. If you execute the following line of code

SELECT dbo.SubtractNumbers(10,null)

the generated result is

-----------
.Net SqlClient Data Provider: Msg 6569, Level 16, State 1, Line 1
'SubtractNumbers' failed because input parameter 2 is not allowed to be null.

.NET User-Defined Functions and the SqlFunction Attribute

If you know that you will not access any database objects in your user-defined functions you can add the SqlFunction attribute and set the DataAcess and SystemDataAccess properties to None. This way SQL Server can optimize the execution of user-defined functions that do not use the SQL Server InProc Data Provider. The use of the SqlServer InProc Data Provider will be covered in a follow-up article. The resulting code is show below.

namespace MathTutor
{
     public class Math
     {
          [SqlFunction(DataAccess = DataAccessKind.None, 
                      SystemDataAccess = SystemDataAccessKind.None, 
                      IsDeterministic = true, IsPrecise = true)]
          public static SqlInt32 AddNumbers(SqlInt32 i, SqlInt32 j)
          {
               return i + j;
          }

          [SqlFunction(DataAccess = DataAccessKind.None, 
                      SystemDataAccess = SystemDataAccessKind.None,
                      IsDeterministic = true,IsPrecise = true)]
          public static int SubtractNumbers(int i, int j)
          {
               return i - j;
          }

          public static SqlInt32 IncrementBy(SqlInt32 by, ref SqlInt32 number)
          {
               int retValue = 0;
 
               try
               {
                    number += by;
               }
               catch (Exception ex)
               {
                    retValue = 1;
               }
               return retValue;
          }
     }
}

Also note that the IsDeterministic property is used on the SqlFunction attribute to mark the function as being deterministic. Deterministic functions always return the same result any time they are called with a specific set of input values.

User-defined functions can not only be used in SELECT statements but also for the definition of computed fields in a table. In the following table there is a field called Addition that contains the result of the execution on the AddNumbers user-defined function.

CREATE TABLE Numbers
(
Number1 INT,
Number2 INT,
Addition AS dbo.AddNumbers(Number1, Number2) PERSISTED
)

The PERSISTED keyword tells SQL Server to physically store the computed values in the field of the table. The value in the computed field will automatically be updated whenever one of the dependent fields change.

Since the Addition field is persisted and since the user-defined function is marked as deterministic you could create an index on this computed field.

CREATE INDEX idx ON Numbers(Addition)

This would not be possible is AddNumbers was not deterministic since the return value of the AddNumbers function would be different every time it's called wilh a specific set of input values.

Registering .NET Stored Procedures in SQL Server

To register a method from an assembly as a stored procedure you use the CREATE PROCEDURE statement together with the EXTERNAL NAME clause.

CREATE PROCEDURE IncrementBy (@by INT, @number INT OUTPUT)
AS EXTERNAL NAME MathTutor:[MathTutor.Math]::IncrementBy

To execute the stored procedure "IncrementBy" the following T-SQL code can be used.

DECLARE @result INT
DECLARE @number INT
SET @number = 111
EXEC @result = IncrementBy 10,@number OUTPUT
PRINT @result
PRINT @number

Conclusion

In this first article we covered the integration between the .NET Framework and SQL Server "Yukon". You saw how stored procedures, user-defined functions and triggers can be created in .NET and used within SQL Server "Yukon".