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.
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.
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)