SQL 2000 User Defined Function Primer
July 5, 2000
Many programming languages have supported User Defined Functions for years, but they are new to SQL Server 2000. In this article we will look at some of the ways functions can be used within SQL Server 2000.
This document was developed against SQL Server 2000 Beta 2, so some points may be subject to change in the release version.
What are functions?
Functions are pre-prepared pieces of code that may accept parameters, and
always return a value. Older versions of SQL Server supply some built-in
functions, such as
Throughout the text I will use the abbreviation UDF for User Defined Function.
All functions (not just UDFs) are split into two groups, deterministic and non-deterministic. Deterministic functions will always return the save value if provided with the same parameters. Non-deterministic functions may produce different results each time they are called, even if any supplied parameters are unchanged.
SQL Server Books Online also uses the term "scalar functions"--these are simply functions that return one value, as opposed to functions that return a table.
A simple UDF
It's not uncommon to want to know the number of days in particular month, so let's look at a simple function to do this. Here is the code:
To execute the function,
As you can see, most of the code could have come from any SQL Server stored procedure. The new features are:
Using function calls
You can code a function call anywhere you could code a variable or literal of the same data type as the function returns. For example, to calculate the number of days between now and the end of the month:
Note the repeated use of the owner qualification before the UDF name, (
Functions within functions
You can nest a function within another of you so wish.
Restrictions on using functions within functions
There is a restriction on this which I ran straight into when I first started to play with UDFs. You cannot use a built-in non-deterministic
function within a user defined function. When I made my first stab at coding
Using other data types
You can return almost any data type in a function, including User Defined Data Types. The following simple example returns a UDDT based on the varchar data type.
SQL Server 2000 will not allow you to return text, image, cursor or timestamp data types from a function.
Functions have the ability to call themselves, or "recurse". The example below works out the factorial of the integer input.
For the non-mathematically inclined, factorials are what you get when you multiply together all the whole numbers between 1 and the number you thought of. For example:
Functions can recurse up to level 32 deep, after which SQL Server will generate an
error. The example above is restricted to 20 times because Factorial
21 is too big for the
Passing tables out of functions
Create a function that passes back a table and what you end up with is a kind of "parameterized view"--something you can treat as a view, but which will return different values depending on the parameters passed in. The following example for the PUBS database takes an author_id as it's parameter and returns a table containing all the author IDs that have collaborated with them, and the title_id they worked together on.
Note the use of the COALESCE function. We use this to return data for all authors that have collaborated on a book if no specific author_id is given.
You could code the line
In-line table functions
SQL Server differentiates between "user defined functions that return a table" and "in-line table functions". The previous example is a "user defined functions that return a table". We can re-code this as an in-line function as follows:
This syntax is more compact than the previous example, but also more restrictive. While the "user defined functions that return a table" can include any amount of code, the in-line version is restricted to a single select statement. Even the "order by" clause needs to be removed from the in-line version.
Table function in joins
Parameterized functions that return tables may be part of a standard SQL join statement.
Functions as columns in views
UDFs can be used as columns in a view.
Building on the
Functions and constraints
You can use functions as to define constraints, provided that both the table
and the function share the same owner. This example defines a function called
"midnight" that truncates a date-time to midnight, then uses this in
a table's check constraint to ensure that the
You can also use a function as the basis for a default constraint, but presently you are limited to using constants in the function parameters, which restricts their usefulness.
Schema bound functions
Particularly with older versions of SQL Server, you could get into trouble if you defined one object that was dependent on another, then dropped or changed the subordinate object. (In plain English: if you created a view on a table, then dropped the table, the view would still exist but it would obviously not work.)
Schema binding tries to put a stop to this. If you create a function and specify it is Schema Bound, then the objects it depends on cannot be altered without you first dropping the schema binding. There are limitations as to when this will work (it will not work across multiple databases for example) but it is still a very useful feature.
Schema binding is invoked at function creation time. We can change our
As long as schema binding is in place for this function, any attempt to
change the structure of the
Overloading is the practice of having multiple functions with the name name, each doing different things. The '+' operator is overloaded as it can both add up numbers and concatenate strings.
In Beta 2 at least, overloading functions is a non-starter due to the UNIQUE
constraint on the