SQL Server ships with a collection of built-in functions that enable you to perform a variety of operations. Each built-in function serves a specific purpose, and can’t be modified. If a function serves your purpose, you can go ahead and use it.

But what if you can’t find a function that serves your purpose?

Then it’s time to write your own user-defined function.

What is a User-Defined Function?

A user-defined function (UDF) is a routine that can take parameters, perform calculations or other actions, and return a result. And as the name suggests, it is defined and created by the user. In this case, the “user” is the SQL programmer, DBA, or whoever wrote the function.

The term user-defined function distinguishes it from the built-in functions that are shipped with SQL Server.

You can specify whether or not the UDF will accept any parameters, and if so, their names, data types, etc. For example, you could write a UDF that accepts a product ID as an argument. The function can then use that in its calculation. This means that the output of the function will depend on the input that is supplied to it when it’s invoked.

Once a UDF has been created, it can then be invoked.

Invoking a User-Defined Function

The code inside the function is not executed when the function is created. It’s only executed when the function is invoked.

“Invoking” a function is sometimes referred to as “calling” a function. This is when you get the function to do what it was made to do. Basically, you create a function, and then it just sits there waiting to be invoked.

You can invoke a UDF from your T-SQL code, just like you’d call a system function. For example, you could use a UDF in a WHERE clause in order to narrow the results of a SELECT statement. You could also use it in the list of columns to return.

UDFs can also be used in computed columns. This can be handy if you ever need a computed column to access data in another column.

Parameters

A user-defined function can accept up to 1024 input parameters. However, you can also define a function without any parameters if required.

An example of a built-in function without any parameters is GETDATE(). This function simply returns the current database system timestamp. No need to pass any parameters to get that.

An example of a user-defined function with a single parameter could be one that returns a customer’s contact details based on the customer’s ID. When you call the function, you pass the customer’s ID to the function as a parameter. The function can then look up the customer’s details and return them in the return variable. If you pass in a different customer’s ID, the function will return the details for that customer.

Benefits of User-Defined Functions

Some of the main benefits of UDFs include the following.

Modular programming

UDFs allow you to write code once, then call it as many times as needed. No need to rewrite the same code each time you need to do the same thing. Simply call the function instead. This is also beneficial if something changes. You only need to update it in one place – the function. If you didn’t use the function, you’d need to update it multiple places throughout your application.

Performance

UDFs reduce the compilation cost of the T-SQL code by caching the plans and reusing them for repeated executions. The UDF doesn’t need to be re-parsed and re-optimized with each use. This results in much faster execution times.

Reduced network traffic

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

UDFs can also have other, more specific benefits, like in the previous example that I mentioned with the computed column accessing data in another table.

Types of User-Defined Functions

There are two types of T-SQL user-defined functions:

Scalar Functions

User-defined scalar functions return a single data value. You define the value’s type in the function. The return type can be any data type except text, ntext, image, cursor, and timestamp.

Table-Valued Functions

Table-valued functions (TVFs) return a table. Their return type is therefore table. There are two types of TVF: inline TVF, and multi-statement TVF. An inline TVF doesn’t have a function body. Its return table is the result of a single SELECT statement. A multi-statement TVF on the other hand, does have a function body. With multi-statement TVFs, you specify the structure of the table in the return variable.

You can also create scalar and table-valued functions as common language runtime (CLR) user-defined functions.

Beginning with SQL Server 2005 (9.x), you can write user-defined functions in any Microsoft .NET Framework programming language, such as Microsoft Visual Basic .NET or Microsoft Visual C#.