Creating User-Defined Functions in Microsoft SQL Server


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:windowsmicrosoft.netframeworkv2.0.50727csc.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.

Latest Articles