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
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
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