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.
Note
Transact-SQL user-defined functions in queries can only be executed on a single thread (serial execution plan).
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)