User Defined Functions in SQL Server: A Complete Guide

In this introduction tutorial, we will discuss one of the most common SQL Server objects you will see in your career working with SQL Server: User defined functions.

There are three types of user defined functions in SQL Server:

• Scalar Valued Functions
• Inline Table Valued Functions
• Multi Statement Table Valued Functions

Be sure to check out those links for a complete understanding of each of the function types. In this introduction tutorial, we will give an overview of each function type as well as discuss the common characteristics that all the function types share. We will discuss these topics:

1. What are user defined functions, and how can they help us?
2. How are System functions different from user defined functions? 
3. Scalar Valued Function
4. Inline Table Valued Function
5. Multi Statement Table Valued Function
6. What do all function types have in common?
7. Are there any limitations on functions?
8. Tips, tricks and links

Everything discussed in this tutorial can also be found in the following FREE Ebook:

FREE Ebook on SQL Server User Defined Functions!

This FREE Ebook contains everything you need to know about all the different User Defined Functions in Microsoft SQL Server. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

Let’s get into it:

1. What are user defined functions, and how can they help us?

A user defined function is a database object that is used to save a lengthy or complicated query so that it can be ran repeatedly using simple commands. A function can return a single value back to the caller, or it can return a result set. Through the use of parameters, functions can also be made dynamic.

If you already have experience programming, you will be familiar with the idea of a function. Basically, a function saves us time by allowing us to write a lengthy or complicated query one time, and then call that function using simple commands every time we need that work performed.

For example, take the following query:

This query will calculate how much money our company has made from a single product (in our example, that product has a Product ID of 1).

Functions provide repeatability

If we wanted to repeat this same query for other products, we could just repeat the query and plug in a different Product ID each time. But wouldn’t it be easier to write this query one time and somehow save it? It would save us lots of time and would create less lines of code.

Through the use of a Scalar Function, for example, we can do that. I created a Scalar Function called ‘getProdSum‘, and it does the same work as the query above. Here is how easy it is to call:

The parameter to the function is the Product ID, 1. Here is what the definition of the function looks like:

We can see this function takes in a parameter, and that parameter is used to make the output of the function dynamic. In other words, the output of the function depends on the Product ID you pass in.

If you don’t understand the code above, don’t sweat it. We’re going to break it down further in this tutorial, and we will break it down completely in the in-depth tutorial:

Scalar Valued Functions: The Ultimate Guide for Beginners

2. How are System functions different from user defined functions? 

A user defined function is something you create. It is NOT something shipped out-of-the-box with Microsoft SQL Server.

But there are, in fact, “out-of-the-box” functions that come with Microsoft SQL Server. In fact, there are dozens of them. They are called System functions.

There are System functions to help you with several different tasks in SQL Server, including:

• Aggregating data
  • Ex: SUM(), COUNT(), MIN(), MAX()
• String manipulation
  • Ex: LEN(), SUBSTRING(), LEFT(), RIGHT(), CHARINDEX()
• Date and time calculations
  • Ex: GETDATE(), DATEDIFF(), DAY(), MONTH(), YEAR()
• Mathematics
  • Ex: AVG(), ABS(), SIN(), COS(), TAN()
• And many, many more!

Do yourself a favor: Don’t try to memorize all the System functions and what they do! Learn them as you go. There are certain ones I use all the time (like GETDATE(), SUM(), COUNT()), and other’s I rarely use or have never used. So learn them as you go.

The focus of this tutorial is on user defined functions, which as I said, are functions created entirely by you.

3. Scalar Valued Function

A Scalar Valued Function is a function that returns a single value back to the caller. A Scalar Valued Function can be used anywhere a single value is expected. You can pass in one or more parameters to a Scalar Valued Function and do work based on those parameters. 

The example I gave in point # 1 is an example of a Scalar Valued Function. Basically, you would use this type of function when you need to derive and return a single value. That value can be a number, like in my example, or it can be any other data type like a character string, date, bit, etc.

Here is the general layout of a Scalar Valued Function:

CREATE FUNCTION functionName(<optional parameter list>)
RETURNS <data type of return value> 
AS 
BEGIN 
<function body> 
RETURN <value to return> 
END

And here is that example again of a Scalar Valued Function definition:

Here is how you would call that function:

For an in-depth look at Scalar Valued Functions, be sure to check out the tutorial here:

Scalar Valued Functions: The Ultimate Guide for Beginners

 4. Inline Table Valued Function

An Inline Table Valued Function returns a result set to the caller by way of an inner SELECT statement that is part of the function definition. You can query an Inline Table Valued function similar to how you would query a table. They accept input parameters which allow them to return a different result set depending on the parameters passed in.

You would query an Inline Table Valued Function the same way you would query a table. For example, you would call an Inline function in the FROM clause of a query.

The general layout of an Inline Table Valued Function is like this:

CREATE FUNCTION <schema>.function_name(<optional_parameters>) 
RETURNS TABLE 
AS 
RETURN 
<SELECT statement>

Here is an example of an Inline Table Valued Function Definition:

Here is how you would call that function:

This particular function will return a single row, but you can certainly have Inline Table Valued Functions that return multiple rows.

For an in-depth look at Inline Table Valued Functions, check out the full tutorial:

Inline Table Valued Functions: The Ultimate Guide for Beginners

5. Multi Statement Table Valued Function

A Multi Statement Table Valued function returns the result set of a table variable that is created, defined, and populated within the definition of the function. The advantage of a Multi Statement Table Valued function is you have complete control over the definition of the table variable, including naming your columns, as well as setting up appropriate data types, constraints, indexes, etc. 

Unlike Inline Table Valued functions, you have complete control over the columns you choose to return from the function. An Inline Table Valued function will pull already-existing columns from already-existing tables. With Multi Statement Table Valued functions, you create the table variable whose result set will be returned to the caller.

Like Inline Table Valued Functions, a result set will be returned to the caller, meaning you can query a Multi Statement Table Valued Function similar to how you would query a table.

The general layout of a Multi Statement Table Valued Function is like this:

CREATE FUNCTION <schema>.function_name(<optional parameters>) 
RETURNS @table_variable_name TABLE 
( 
<table variable definition> 
) 
AS 
BEGIN 
<function body to populate @table_variable_name> 
RETURN 
END

Here is an example of the definition of a Multi Statement Table Valued Function:

Here is an example of how you would call that function:

For the in-depth look at Multi Statement Table Valued Functions, take a look at the full tutorial:

Multi Statement Table Valued Functions: The Ultimate Guide for Beginners

6. What do all function types have in common?

If you have been skimming through my examples, you might have noticed they all have certain things in common. Here is a rundown of the parts that all user defined functions share:

All user defined functions need  an outer RETURNS clause.

All functions have an outer RETURNS clause. Notice the ‘S‘ at the end. This outer RETURNS clause says what will be returned to the user. In the case of Scalar Functions, it outlines the data type of the value we will be returning. For Inline Table Valued Functions, it simply outlines that we will return a TABLE. For Multi Statement Table Valued Functions, it outlines the actual name of the table variable we will be returning. 

All user defined functions need an inner RETURN clause.

They all have an inner RETURN clause, too. This inner RETURN clause is what passes control back to the caller. In the case of Scalar Functions, the RETURN clause will return the value derived in the function definition. In the case of Inline or Multi Statement Table Valued Functions, it will return the result set derived in the function definition.

When you call a user defined function, you need to outline the schema the function belongs to.

When we call a user defined function, we need to outline the schema the function belongs to. Notice all our functions belong to the ‘dbo‘ schema, but they certainly could have been put in a different schema. In the function definition, if we wanted the function to be put in a different schema, we would have needed to specify that schema name. Since we didn’t explicitly outline a schema in our Scalar or Inline function definitions, they were put in the ‘dbo’ schema automatically (and notice in our Multi Statement definition we chose to explicitly put the function in the ‘dbo‘ schema).  

When you call a user defined function, you need to use parentheses around the parameters, even if the function does not have parameters.

When we call a user defined function, we need to use parentheses for the parameters, even if the function does not have parameters. All our examples do, in fact, have parameters, but you can certainly make a user defined function that does not have any parameters. In that case, you would still need to use parentheses when you call the function. You would just leave the parentheses empty. 

When you define a user defined function, you need to use parentheses around the parameters, even if the function does not have parameters

Similarly, when we define a user defined function, we need to use parentheses for the parameters, even if the function does not have parameters. This is very similar to the last point. All our examples do, in fact, have parameters, but you can certainly make a user defined function that does not have any parameters. In that case, you would still need to use parentheses when you define the function. You would just leave the parentheses empty.

(But most of the time, our functions are going to have parameters)

7. Are there any limitations on user defined functions?

Yes.

Here is a list of things you cannot do in a user defined function:

• No error handling code in your function – You cannot use TRY…CATCH blocks, for example.
• You cannot use temporary table – You can’t write table results to a temp table within a function.
• No dynamic SQL – You can’t write a command as a string literal, then call ‘execute’ on that string literal. For example, we cannot say execute ‘SELECT ProdName FROM Products’ from within a function. 
• You cannot use Data Definition Language inside a function –  You can’t use CREATE, ALTER, or DROP statements. We couldn’t make a new table from within a function if we wanted to, for example.
• No DML statements within a function – You can’t issue INSERT, UPDATE, or DELETE statements. The only exception to this rule is if you were inserting, updating, or deleting data in a table variable, which is what we use in a Multi Statement Table Valued Function.

8.  Tips, tricks and links

Here is a list of some helpful tips and tricks you should know when working with functions in SQL Server.

Tip # 1: Use the ALTER FUNCTION statement to change a function

If you need to change your user defined function, you can use the ALTER FUNCTION statement, followed by the function definition like so:

Notice you don’t need to specify the schema for ALTER operations either.

Also, understand you cannot just modify a single part of the function. You need to rewrite the entire function.

Tip # 2: Use the Object Explorer in SQL Server Management Studio to easily see the user defined functions that exist for your database

In the Object Explorer, expand the tree for your database, then expand Programmability | Functions, like so:

Scalar Valued Functions would be under the Scalar-valued Functions tree (shocking, I know), and both Inline and Multi Statement Table Valued Functions would be under the Table-valued Functions tree. While we’re at it, all the System functions (functions that come with SQL Server) are in the System Functions tree.

Also, if you want to quickly see the definition of a function, you can use the Object Explorer. You would  navigate to the function, right-click on it and choose Modify, as shown here:

This will open the definition of the function in a new query window. The function will be ready for you to ALTER, as pointed out here: 

Finally, if you want to easily see the parameters for a function, you can also do that in the Object Explorer. Just expand the function, then the Parameters folder:

Tip # 3: Delete a function using the DROP FUNCTION statement

To delete a function, you would run the following statement:

DROP FUNCTION <schemaName>.functionName

The <schemaName> is optional. You can leave it off and SQL will know where to delete it from (as long as there is only one instance of the function name, that is). Here is an example:

Notice there is no need to specify the function definition, just the DROP FUNCTION statement followed by the name of the function. Also notice you don’t need to specify the parameters or the parentheses. In fact, if you try, you will get error messages.

Links

There is a very helpful book out there called Clean Code that discusses the importance of non-repeating code.

Remember, as a developer, you should always try to limit the amount of code you repeat. If you find yourself repeating a certain lengthy, complicated block of code, you should strongly consider writing it as a function instead, and then just call the function every time you need that work performed.

Clean Code discusses the importance of non-repeating code, as well as many other best practices when it comes to programming. You should get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

Don’t forget to download your FREE Ebook:

FREE Ebook on SQL Server User Defined Functions!

This FREE Ebook contains everything you need to know about all the different User Defined Functions in Microsoft SQL Server. This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Make sure you download it today!

As a database professional, it is important you know all about the o

Be sure to check out the in-depth tutorials on all the user defined function types. Here are the links:

1. Scalar Valued Functions: The Ultimate Guide for Beginners

2. Inline Table Valued Functions: The Ultimate Guide for Beginners

3. Multi Statement Table Valued Functions: The Ultimate Guide for Beginners

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, leave a comment. Or better yet, send me an email!