User-defined functions (UDFs) – Part-1

Author: Basit A. Farooq

Editor’s Note: In this first of the two part article series, you’ll compare and contrast stored procedures for creating and using scalar valued functions, in-line table functions, and multi-statement table functions. You’ll learn about syntax and parameters of CREATE FUNCTION statement.

User-defined functions (UDFs) are similar to system functions, except that you create them to meet your specific needs. They are also similar to stored procedures, except that user-defined functions don’t support OUTPUT parameters. Instead, a user-defined function returns a value. The type of value returned depends on the type of user-define function. The one of the two most notable differences between stored procedures and user-defined functions is that user-defined functions can be used in the SELECT statement, and you can join them to tables, views, CTE and even other functions. The second difference is that you can perform DML operations within stored procedures, but you cannot perform DML operations within the user-defined functions. SQL Server supports Transact-SQL and CLR user-defined functions. The difference between the two is that a Transact-SQL user-defined function is based on Transact-SQL statements and a CLR user-defined function is based on a registered assembly method. In general, CLR user-defined functions are more suitable for computational tasks, string manipulation, and business logic, while Transact-SQL user-defined functions are more suitable for data-access intensive logic. A detailed discussion of CLR based user-defined function is beyond the scope of this article. For help with this, see the “Create CLR Functions” section of SQL Server Books Online.

Transact-SQL user-defined functions basics

The Transact-SQL user-defined functions are flexible programmable objects that extend the functionality of SQL Server.

Transact-SQL user-defined function types

Functions are defined by the function’s structure and the type of value it returns. There are three types of Transact-SQL user-defined functions:

• Scalar-valued function — Returns a single value.
• In-line table-valued function — Returns a table resulting from a single query statement.
• Multi-statement table-valued function — Returns a table resulting from multiple T-SQL statements.

Scalar functions are similar to system functions and you can use them in the same manner. Typically, any place you can specify a system function, you can specify a UDF. SQL Server also supports a specialized CLR function type, called an aggregate function. An aggregate performs calculations on supplied values to return a scalar value. Table-valued functions are similar to views in their use, but are more flexible because they support input parameters. This lets you control the query used to retrieve the table result.

User-defined functions advantages

Many of the advantages of using functions are the same as those of using stored procedures. These include:

• Faster execution — Statements in a function execute as a single code block. The function is parsed, optimized, and compiled the first time it is used (first time after SQL Server restarts) and loaded into the procedure cache. The execution plan is reused from the cache on subsequent executions.
• Modular programming design — Functions are reusable objects that let you automate repetitive tasks. You can also call functions from scripts or stored procedures, extending their usefulness. The modular design means you can modify the internal code of a function, without necessarily having to modify calling procedures or scripts.
• Reduced network traffic — One common use of UDFs is as qualifying logic in WHERE statements when simple expressions don’t meet your needs. By better qualifying the data, you reduce the number of rows (and amount of traffic) returned to client computers.

User-defined functions

You can use UDFs in the same ways you use views and system functions. This includes:

• As a data source in a query’s FROM clause (table-valued functions)
• To provide a value in a query’s SELECT clause (scalar-valued functions)
• To implement qualifying logic in a query’s WHERE clause (scalar-valued functions)
• In column and check constraint definitions
• To return values or tables to applications
• When creating a stored procedure or another function

You can sometimes use user-defined functions as replacements for stored procedures. It is easier to integrate user-defined functions into applications and other procedures than to write the code required to retrieve return values and OUTPUT parameters from stored procedures.

You cannot use a user-defined function to replace:

• A stored procedure that updates data.
• A view that is used for data updates (updates the base tables).

You must use the two-part name (schema.name) when you call a user-defined function.

User-defined function limits

Transact-SQL user-defined functions can include:

• Statements that declare and set values for local variables (using SET or SELECT).
• Cursor FETCH statements (used to retrieve values from cursors) that assign values to local variables.
• Control of flow and error handling statements, except TRY…CATCH.
• INSERT, UPDATE, and DELETE statements that modify table variables.
• EXECUTE statements that call extended stored procedures.
• Deterministic functions.
• A subset of SQL nondeterministic functions.

You can include the following nondeterministic system functions in a user-defined function:

• CURRENT_TIMESTAMP
• GET_TRANSMISSION_STATUS
• GETDATE
• GETUTCDATE
• @@CONNECTIONS
• @@CPU_BUSY
• @@DBTS
• @@IDLE
• @@IO_BUSY
• @@PACK_RECEIVED
• @@MAX_CONNECTIONS
• @@PACK_SET
• @@PACKET_ERRORS
• @@TIMETICKS
• @@TOTAL_ERRORS
• @@TOTAL_READ
• @@TOTAL_WRITE

Information: The deterministic functions return a same value every time, while the output of non-deterministic functions is based on the values ??of the input parameters.

Creating user-defined functions

You create user-defined functions with the CREATE FUNCTION command at the command line and in SQL Server Management Studio.

Creating functions by using SQL Server Management Studio

When you create a function in SQL Server Management Studio, you choose the type of function: scalar-valued, in-line table valued, or multi-statement table valued. This opens a query window with the appropriate template loaded. The advantage is that SQL Server provides the template and the syntax specific to the type of function you selected.

Creating scalar functions

The CREATE FUNCTION syntax varies depending on the type of function you create. The syntax to create a scalar-value function is:

CREATE FUNCTION [schema.]function_name

([@parameter_name data_type [= default]])

RETURNS return_data_type

[WITH function_options] [AS]

BEGIN

SQL_statements

RETURN scalar_expression

END

The following describes this CREATE FUNCTION statement command parameters:

• schema – Species the name of the schema in which you are creating function.
• function_name – Specifies the name of the function, must be unique within the schema.
• @parameter data_type – Defines function input parameters.
• WITH function_options – Options used to further define the function options. These are:
• ENCRYPITON – Encrypts the text of the CREATE FUNCTION statement.
• SCHEMABINDING – Binds the function to the underlying base objects, meaning users cannot modify the underlying base object in any way that affects the function definition.
• EXECUTE AS – Specifies the context under which the stored procedure executes. You can set execute context as CALLER, SELF, OWNER, or user_name, to identify a specific user.
• RETURNS – Sets return value data type; scalar data type only for scalar-valued functions
• SQL_statements – Statements to generate the return value
• RETURN – Function returns value result

You must follow the identifier guidelines with the function and parameter name. You can specify the parameter and RETURNS data type as a system, CLR user-defined, or alias type, with the following restrictions:

• Transact-SQL user-defined functions don’t support timestamp data type.
• CLR user-defined functions don’t support text, ntext, image, char, varchar, varchar(max), and timestamp.

If you don’t specify any parameters, you must still include an empty pair of parentheses.

Scalar functions support the following options:

• ENCRYPTION — Encrypts the CREATE FUNCTION statement.
• SCHEMABINDING — Binds metadata schema of objects referenced by the function.
• EXECUTE AS — Sets the execute context as CALLER, SELF, OWNER, or user_name, to identify a specific user.

SQL Server supports schema binding for Transact-SQL functions only. Requirements for using SCHEMABINDING include:

• You must reference objects by using the two-part (schema.name) object name.
• You must create referenced UDFs and views by using SCHEMABINDING.
• Referenced objects must reside in the same database as the function.

Scalar functions also support RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT. These define the OnNULLCall attribute of scalar functions as:

• CALLED ON NULL INPUT — (default) Function executes if NULL passed as an argument.
• RETURNS NULL ON NULL INPUT — Function returns a value of NULL if NULL is passed as an argument.

For example, the following function returns the day of week:

CREATE FUNCTION ops.fn_gettoday

()

RETURNS varchar(20)

AS

BEGIN

RETURN datename(dw,GETDATE())

END

To use the function, run:

SELECT ops.fn_gettoday()

A variation on the function has you enter the date:

CREATE FUNCTION ops.fn_getday

(@qdate datetime)

RETURNS varchar(20)

AS

BEGIN

RETURN datename(dw,@qdate)

END

You must provide a date when you call the function, as in the following:

SELECT ops.fn_getday (‘July 31, 2010’)

The following variation includes control-of-flow language:

CREATE FUNCTION fn_weekend (@qdate datetime)

RETURNS varchar(20)

AS

BEGIN

DECLARE @wkday varchar(20)

IF (datename(dw, @qdate) = ‘Saturday’

OR datename(dw, @qdate) = ‘Sunday’)

SET @wkday = ‘Weekend’

ELSE

SET @wkday = ‘Workday’

RETURN (@wkday)

END

The function checks the day, then returns either Weekend or Workday as appropriate.

Continue to Part-2…