Creating User-Defined Functions in Microsoft SQL Server

September 27, 2010

SQL Server’s own list of functions is limited as far as what is available for database developers to use in their applications. Starting with SQL Server 2000, database developers were given the ability to create their own routines that accept parameters, perform custom actions and return results.

SQL Server's own list of available functions is not enough for database developers to use in their applications, especially when it comes to business logic, complex algorithmic calculations and custom actions. In this light, beginning with SQL 2000, SQL Server allows database developers to create their own routines, which can accept parameters, perform custom actions and return results. These routines are called user-defined functions.

Initially SQL Server only allowed user-defined functions in T-SQL. Following CLR integration in the SQL Server engine, the later versions of SQL Server (including SQL Server 2008 R2) allow users to create user-defined functions (called UDFs from here on) in a managed code, in addition to T-SQL.

With UDFs, database developers can express their business logic in a form they know best and get their business logic executing in the SQL engine from where it can be called in their SQL queries.

Benefits of User-defined Functions

Below are the most visible benefits of user-defined functions.

  • Extend programming richness via user code - Your database can now do more than only process data. With UDFs, databases can now also validate business logic and do custom actions.
  • Faster performance than T-SQL functions for computational logic - T-SQL functions are great at processing data. When it comes to business logic and custom algorithms, UDFs can do a much better job. UDFs work akin to stored procedures.
  • Optimizes network traffic - UDFs allow data manipulation such that you don't need a lot of talking to the database and you can optimize your results based on your business logic within the database. This reduces network bandwidth utilization.
  • Reusable code, which can be called akin to stored procedures with business logic.

Limitations of User-defined Functions

When called in SQL queries, UDFs are only executed in a single thread. They follow serial execution plan. Hence, if the business logic inside UDFs would work better in a multi-threaded environment, it would be better to not implement it as a user-defined function, since it would lead to over-all performance degradation.

UDFs cannot change database state so there might be usage constraints if you are attempting to change the database information inside your business logic.

Where are User Defined Functions Used

The following is a list of most common usage area of UDFs.

  • In T-SQL statements, such as select
  • Inside the definition of another User-defined function
  • As a replacement of stored procedures
  • For defining automatic values for a column in a table

Structure of User-defined Function

UDFs are composed of two parts:

  1. Header
  2. Function body

The header contains the function name, input parameter info (name and type), and return parameter info (name and type). The function body contains the logic. It contains one or more T-SQL statements that contain the logic and it can also contain a reference to a .NET assembly (in case of a CLR UDF).

Types of User-defined Functions

Based on the return type, UDFs can be classified as scalar functions and table-value functions.

Scalar Functions

Scalar functions are UDFs, which return a single value.

There is no function body in the case of an inline scalar function, and the scalar value (the return value) is the result of a single statement.

When to choose?

Scalar functions are preferred when you want a single value as the result. Examples might be the total cost of a business trip, total project cost in man-days or the birth date of a person.

The return type can be any valid SQL type except text, ntext, image, cursor and timestamp.


  CREATE FUNCTION dbo.CalculateTotalDaysForProject(@DevelopmentDays int, @TestingDays int)
  RETURNS int
  AS
  BEGIN
  DECLARE @totalcost int;
  SELECT @ totalcost = @DevelopmentDays + @ TestingDays;
  RETURN @totalcost;
  END

Listing 1 - Example of a scalar function Table-values Functions

Table value functions

Table value functions are UDFs that, as the name suggests, return a table.

When to choose?

Table value functions are used when one or more row's worth of data is expected. Examples would be all orders generated by the best performing salesperson, etc.

  CREATE FUNCTION GeneratedSales.SalesByPerson(@sales_ID int)
  RETURNS @SalesData TABLE
  (
  [CustomerID] int,
  [SalesAmount] int,
  [SalesDate] date
  )
  AS
  BEGIN
  INSERT INTO @SalesData
  	SELECT Sales.CustomerID, Sales.SalesAmount, Sales.SalesDate from Sales 
  Where Sales.Sales_ID = @sales_ID
  RETURN
  END

 Listing 2 - Example of a table-value function

Creating User-defined Functions

User-defined functions are created by the "CREATE FUNCTION" statement. They can be edited by using the "ALTER FUNCTION" statement and deleted by the "DROP FUNCTION" statement.

One key thing to keep in mind is that statements within the BEGIN..END block cannot make any changes like updates to the database. Only local changes (objects in the local scope, such as temporarily created tables and variables) are allowed.

Valid T-SQL statements allowed in User-defined functions include DECLARE, SELECT, TRY…CATCH, EXECUTE, UPDATE (local only), INSERT(local only), DELETE (local only).

Most deterministic T-SQL built-in functions can be used inside a UDF.

CLR user defined functions

Creating a CLR UDF is a multi-stage process.

First, you define the desired function as a static method of a class in the .NET framework language.

For example, let us create a function in C#, which lists the count of unique salespersons that made sales in the last quarter.

  using Microsoft.SqlServer.Server;
  using System.Data.SqlClient;
  
  public class Sales
  {
      public static int GetCountOfUniqueSalesPersons()
      {
          using (SqlConnection myConnection
              = new SqlConnection("context connection=true"))
          {
              myConnection.Open();
              SqlCommand myCmd = new SqlCommand(
                  "SELECT COUNT(DISTINCT SalesPersonID) AS 'UniqueSalesPersons' FROM SalesOrders", myConnection);
              return (int)myCmd.ExecuteScalar();
          }
      }
  }

 Listing 3 - CLRfunction.cs

Now compile this as a library.

  C:\windows\microsoft.net\framework\v2.0.50727\csc.exe /t:library /out:CLRUDF.dll CLRFunction.cs

Next, we register this assembly with SQL using the CREATE ASSEMBLY statement.

  CREATE ASSEMBLY CLRUDF  FROM 'CLRUDF.dll';

Lastly, we create a function that references the newly registered assembly using the CREATE FUNCTION statement.

  CREATE FUNCTION GetCountOfUniqueSalesPersons() RETURNS INT 
  AS EXTERNAL NAME CLRUDF.Sales.GetCountOfUniqueSalesPersons; 

We can now use this newly available user-defined function.

T-SQL user defined functions

T-SQL UDFs are simpler to create as they only have SQL statements. See above for examples of the Scalar UDF and Table-Value UDFs.

Executing User Defined Functions

UDFs can be executed in a number of places as mentioned above. Let us look at how to invoke UDFs in a simple SELECT statement. The following will list all salespersons that made sales in the last quarter.

  SELECT dbo.GetCountOfUniqueSalesPersons()
  FROM SalesOrders where SalesOrders.QuarterStartDate = '2010-04-01' and Sales.QuarterEndDate = '2010-6-30';

Summary

User defined functions provide an easy way to encapsulate business logic and custom actions and can be used by database developers to optimize their application behavior. I hope that this article will give you ideas on how this feature can be used to make more performance database applications.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers