User-Defined Functions

Previously, I discussed aggregate and built-in scalar functions as ways to calculate and return values from tables. In today's blog post, I am going to discuss User-Defined Scalar functions, which are used to perform calculations that can then be returned to a Stored Procedure, a trigger, the application that called the function or can be integrated into the result set itself. During my working experience, I have only ever had a need to write Scalar-Valued Functions however I have observed as a senior developer has created Table-Valued Functions, but today will be my opportunity to have a go myself!

User-Defined Functions 

As mentioned previously, there are various system functions that are already built into SQL Server. For more on these, please visit my previous blog post entitled: Using Built-In Scalar Functions.

When talking about User-Defined Functions, they are functions which are created by the user and can be stored within any database. However, they can only produce results from a calculation and cannot:

• Modify data within a table. 
• Create or access temporary tables.
• Change the state of a database. 
• Execute code dynamically.
• Call a separate function that has an external effect. 

This does still make them incredibly useful however, as I will explain in the following sections. Other than System and Aggregate Functions, there are 2 other main types of Function that can be defined by the user; Scalar-Valued and Table-Valued.  

Scalar-Valued Functions

Scalar-Valued Functions, exactly like the Built-In Scalar Functions, only return a single value. The only difference is that you, the user, define exactly what goes inside it, plus it is local so can only be used on that particular database whereas the others are global and can be used in any database. An example is shown below (follow the comments in green to see what each line does): 

Table-Valued Functions

Table-Valued Functions return a table data type, which can contain multiple SELECT statements, which could potentially make the function more powerful than a database view (which is restricted to only 1 SELECT).

There are 2 different types of Table-Valued Function; Inline and Multi-Statement. 

Inline 

Inline Table-Valued Functions return a table, with the SELECT statement showing what is returned actually declared within the return statement itself. The result set is populated on the fly and is returned to the user on screen. The example below accepts parameters LowPrice and HighPrice and then returns Items that are only between those 2 values: 

Multi-Statement

Multi-Statement Table-Valued Functions actually declares and creates a form of temporary table that is only accessible within that function and disposes of it once the function has been executed. Obviously, this means that it costs more (memory wise) to use a Multi-Statement function rather than an Inline function. Similarly to the Inline Table-Valued Function, the example below accepts parameters LowPrice and HighPrice and then returns Items that are only between those 2 values. I wanted to do a like for like comparison: 

Inline Vs. Multi-Statement

Typically, Inline Table-Valued Functions are the quicker of the two Table-Valued Functions for the exact reason that the data is populated on the fly. However the negative impact of this is that the result set cannot be manipulated afterwards. Multi-Statement Functions, when used appropriately, can be incredibly powerful as not only can they implement multiple SELECT statements they are much more flexible than Inline Table-Valued Functions. This is why it is incredibly important to decide which function would be best suited for your database needs, otherwise you may be unnecessarily slowing down your processes. 

On a related note, one of the reasons why many developers would prefer to use Views over Table-Valued Functions is because Views can also be optimised using Indexes (a topic which I will be coming onto in the not too distant future!). So make sure you not only choose your data types and sizes carefully, choose your use of functions carefully too! 

As An End Note...

In order to see if either of your Table-Valued Functions have worked correctly, I typed in the following SELECT statements:

And looking at the returning results sets, they worked perfectly!: 

This exercise has been invaluable and I'm sure that I will use functions even more complex than these throughout my career. However, start off small and build your way up. Tomorrow I will be discussing Database Triggers, so stay tuned for that! If you have any more questions on UDFs, please don't hesitate to comment below and I will get back to you as soon as I can.  Thanks!