User-Defined Function Basics

Like functions in programming languages, Microsoft SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

User-defined Function Benefits

The benefits of using user-defined functions in SQL Server are:

  • They allow modular programming.

    You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.

  • They allow faster execution.

    Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

    CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.

  • They can reduce network traffic.

    An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.

Components of a User-defined Function

User-defined functions can be written in Transact-SQL, or in any .NET programming language. For more information about using .NET languages in functions, see CLR User-Defined Functions.

All user-defined functions have the same two-part structure: a header and a body. The function takes zero or more input parameters and returns either a scalar value or a table.

The header defines:

  • Function name with optional schema/owner name

  • Input parameter name and data type

  • Options applicable to the input parameter

  • Return parameter data type and optional name

  • Options applicable to the return parameter

The body defines the action, or logic, the function is to perform. It contains either:

  • One or more Transact-SQL statements that perform the function logic

  • A reference to a .NET assembly

The following example shows a simple Transact-SQL user-defined function and identifies the major components of the function. The function evaluates a supplied date, and returns a value designating the position of that date in a week.

IF OBJECT_ID(N'dbo.GetWeekDay', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetWeekDay;
GO
CREATE FUNCTION dbo.GetWeekDay           -- function name
(@Date datetime)                     -- input parameter name and data type
RETURNS int                          -- return parameter data type
AS
BEGIN                                -- begin body definition
RETURN DATEPART (weekday, @Date)     -- action performed
END;
GO

The following example shows the function used in a Transact-SQL statement.

SELECT dbo.GetWeekDay(CONVERT(DATETIME,'20020201',101)) AS DayOfWeek;
GO

Here is the result set.

DayOfWeek
---------
6  
(1 row(s) affected)

See Also

Concepts

Other Resources