The alphabet is quickly winding down (so sad).  We’re up to the letter U and that stands for User-defined functions.

What is a user-defined function (UDF)?

A function is a unit of code that can accept parameters, perform some action or calculation, and then return the result of that action or calculation. A user-defined function, or UDF, is a function that you create, as opposed to a system function built into the SQL Server Engine (i.e. DATEADD() or SUBSTRING()). They’re especially useful for promoting modular programming within your applications.

Types of UDFs

There are two different types of UDFs in SQL Server, and each has its niche.

Scalar functions are those that return a single value of a scalar datatype, such as int, char, or datetime. An example of a scalar function would be this function from the Adventureworks database. It accepts one integer parameter, the product id, retrieves the current inventory of that product and returns that value as an integer.

CREATE FUNCTION [dbo].[ufnGetStock](@ProductID [int])
RETURNS [int]
AS
BEGIN
DECLARE @ret int;
SELECT @ret = SUM(p.[Quantity])
FROM [Production].[ProductInventory] p
WHERE p.[ProductID] = @ProductID
AND p.[LocationID] = '6'; -- Only look at inventory in the misc storage
IF (@ret IS NULL)
SET @ret = 0
RETURN @ret
END;

Table-valued functions, on the other hand, return a set of values in the form of a table datatype. A simple example of a table-defined function is the following, which returns a list of employees under the specified manager.

CREATE FUNCTION ufnGetEmployees (@MgrID int)
RETURNS TABLE
AS
RETURN
(
SELECT e.EmployeeID, c.FirstName, c.LastName
FROM HumanResources.Employee e
 JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE ManagerID = @MgrID
);
GO

Inline vs multistatement

The two examples I’ve listed above illustrate another element of UDFs: they can be either inline or multistatement. A multistatement function, such as ufnGetStock is one that contains a series of T-SQL statements, bounded by BEGIN and END keywords.

The ufnGetEmployees function is an example of an inline function. There’s no function body, merely a single SELECT statement that produces the returned table. It should be noted that, while BOL states that you can create inline scalar functions, this is in fact an error in BOL. It is not possible to create inline scalar functions in SQL Server 2005/2008. Generally speaking, an inline table-valued function will perform better than a multistatement table-valued function. So keep that in mind when designing your UDFs.

Deterministic vs Non-deterministic

Another property of every function in SQL Server, whether it be a system function or a UDF, is its determinism. The determinism of a function describes whether that function will return the same result every time it’s called with a specific set of parameters. The example functions above are non-deterministic. The results they return for a given parameter value will vary depending on the data in the tables. An example of a deterministic UDF would be the function below. It returns the first monday of the month for whatever date is passed in. Because that first monday will always be the same for a given input date, no matter how many times you execute the function, it is considered a deterministic function.

CREATE FUNCTION ufnFirstMonday (@DateIn date)
RETURNS date
WITH SCHEMABINDING
AS
BEGIN
DECLARE @FirstMonday date
SELECT @FirstMonday = DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,@DateIn),@DateIn) ), 0)
RETURN @FirstMonday
END;
GO

Note the WITH SCHEMABINDING clause. Although the function is logically deterministic, SQL Server won’t recognise it as such without this clause.

Further Reading

For more basic information on designing and creating user defined functions in SQL Server, check out BOL.

SQLCAT talks about how functions work inside SQL Server.