User-Defined Functions

Creating .NET-based user-defined functions (UDFs) is another new feature that's enabled by the integration of the .NET CLR. 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 functions, .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.

To create a UDF using Visual Studio 2005, select the New | Project option and then select the SQL Server Project template as shown in Figure 3-7.

Figure 3-7:Creating a new SQL Server UDF project

As in the Stored Procedure example that was presented earlier, first give your project a name and click OK to create the project. In the example shown in Figure 3-7, you can see that I've used the name ufn_GetDateAsString for my user-defined function. This function returns a string value containing the system date and time. After naming the project, click OK to display the New Database Reference dialog for the CLR Function project, which will resemble the one shown in Figure 3-8.

NOTE: The Add Database Reference dialog is shown instead of the New Database Reference dialog when a database reference has already been created. This would be the case if you created the ufn_GetDateAsString function immediately after the usp_ImportFile project.

Figure 3-8: The New Database Reference dialog

The New Database Reference dialog defines the connection between your Visual Studio project and SQL Server. The project will connect to the SQL Server system named sql2005, and the function will be deployed to the AdventureWorks database.

Once the Visual Studio project has been created and the connection has been defined, you use the Project | Add Function menu option to display the Add New Item dialog that you can see in Figure 3-9.

Figure 3-9: Adding a CLR user-defined function

Visual Studio uses the SQL Server Function project template to create a starter project that includes the reference to the SQL Server .NET Data Provider and a basic function wrapper for your source code. It's up to you to fill in the rest of the code. The following code listing shows the completed CLR function, ufn_GetDateAsString, that performs a basic date-to-string conversion:

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
     
 
   _
     Public Shared Function ufn_GetDateAsString() As SqlString
          Dim dtDataTime As New DateTime
     Return dtDataTime.ToString()
End Function
 

Here, the Microsoft.SqlServer.Server namespace is not needed, as this particular function does not perform any data access. Next, Visual Studio 2005 generated the UserDefinedFunctions class to contain all of the methods that this assembly will expose as UDFs. You can also see that the attribute is used to identify the ufn_GetDateAsString method as a UDF. The code in this simple example just converts the system date to a string data type that's returned to the caller.

Deploying the function

To create the function in a SQL Server database, the assembly must first be created, as you saw in the stored procedure example. Then if you're using Visual Studio 2005, you can simply select the Build | Deploy Solution option and you're done.

If you're doing this manually, you'll need to copy the ufn_GetDataAsString.dll file to a location that's accessible by the SQL Server system and then create the assembly, followed by the function. The following CREATE ASSEMBLY statement can be used to copy the contents of ufn_GetDateAsString.dll into the SQL Server database:

CREATE ASSEMBLY ufn_GetDataAsString
FROM 'MyFileShareCode Libraryufn_GetDataAsString.dll'

The CREATE FUNCTION statement is then used to create a new SQL Server function that executes the appropriate method in the assembly. The following listing illustrates how the CREATE FUNCTION statement can create a .CLR user-defined function:

CREATE FUNCTION ufn_GetDateAsString()
RETURNS nvarchar(256)
EXTERNAL NAME
ufn_GetDateAsString.UserDefinedFunctions.ufn_GetDateAsString

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 ufn_GetDateAsString function is using the assembly named ufn_GetDateAsString. Within that assembly, it's using the UserDefinedFunctions class and the ufn_GetDateAsString method within that class.

Using the function

After the function has been created, it can be called like a regular SQL Server function. You can see how to execute the GetDateAsString function in the following example:

SELECT dbo.GetDateAsString()
End Class

The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.

Developing CLR database objects

 Home: Introduction
 Tip 1: CLR architecture
 Tip 2: CLR assemblies in SQL Server 2005
 Tip 3: Creating CLR database objects
 Tip 4: CLR stored procedures
 Tip 5: User-Defined Functions
 Tip 6: CLR triggers
 Tip 7: User-Defined Types
 Tip 8: CLR aggregates
 Tip 9: Debugging CLR database objects
 Tip 10: .NET database object security