User-defined Function Basics
Like functions in programming languages, Microsoft SQL Server 2005 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
In SQL Server 2005, 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
Understanding User-defined Functions