Understanding User Defined Functions (UDF) in SQL
This topic as such is new to SQL Server terms even
though they have existed in the many other programming languages before. In
this article I would walk you through some of the most common usage of UDFs in
SQL Server 2000 context. Note that some of the tips and tricks used in this
article are undocumented and are based on certain scenarios. Let us first
understand what UDF means on first place. If I were to run to my
quick reference friend SQL Server BOL, it descibes UDF as "UDF are subroutines
made up of one or more Transact-SQL statements that can be used to encapsulate
code for reuse". In other words I can say that UDFs are pre-prepared pieces of
code that return you a valid value as output.
SQL Server in its previous versions did support
built-in functions like getdate(), object_id(), object_name() etc. But
they did not expose an powerful feature to create one. Hence, this is
relatively cool new feature for SQL Server 2000 users. All functions (not just
UDFs) are split into two groups, deterministic and non-deterministic.
Deterministic functions will always returns the same computed 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. I am explaining the same here as we cannot use non-deterministic
functions in a UDF.
In this example we will try to get the last day of the month from an UDF when a
date is passed to the same. For this I would use the example outlined in my
FUNCTION dbo.LastDayInMonth (@when DATETIME)
Declare @lastDate int
SELECT @lastdate = DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@when),@when))))
Hence now if I were to call this function as below I would get the last day of
the current month.
Now that was a cool feature. I often come across where people ask, even a
procedure can return values, right. Basically a procedure cannot be referenced
in our select as we have used our function above. Hence this is a clear
indicator to when we need to use the functions. Moreover you can also notice
that we have mentioned "returns int" indicating that the stored
procedure is going to return an integer explicitly. You can use functions
literally anywhere where you can use an variable or literal of the same
Note: We need to qualify the owner of the function when we reference the functon
in our actual code. This is a mandatory requirement.
Nesting of functions is allowed and it is one feature
we have used unknowingly in our previous example also. We have used built-in
functions like DAY, DATEADD, MONTH etc in our custom function dbo.LastDayInMonth.
Again this is not restricted to using system functions recursively but also
user defined functions can be used recursively.
Now as in most of the books when we talk on recursive
functions the typical example is using a factorial example. And we are no
exception ... So the below code illustrates the use of UDF recursively :
Function dbo.Factorial (@number int)
Declare @finalResult int
If @number < 2
Select @finalResult = @number
Select @finalResult = @number * dbo.factorial(@number - 1)
This is a simple example that returns 40320 for 8!.
But remember we have a nesting of 32 levels supported in SQL Server 2000. But
as mentioned earlier there are other limitations in using such functions also.
The function has to be deterministic in nature always. And take for instance
using getdate() function. This function is non-deterministic and hence
cannot be used inside the UDF. In the last section of my article I'll tell you
how you can use getdate() in your UDF.
Almost any type os datatype can be returned from a UDF. Hence I've included this
section where we will return an UDDT (User Defined Data Type) back from our
UDF. Look at the below code where we have used this concept.
Create Function LowerCase (@string
Select dbo.LowerCase ('TesTing Our
So the example illustrates how we can use a cutom datatype in UDF. And the
datatypes that cannot be used as return are text, image, timestamp and
cursors from a UDF.
This is yet another unique requirement that I've seen in the newsgroups people
ask. You can pass this output as a table from SQL Server. This can be
considered something as parameterized view on the Oracle works. We would
evaluate the various combinations of returning a table from a UDF. For this
example we would use the the Pubs database.
Create Function dbo.TitleTable
Returns @ReturnTable Table
Select title_id, title, type From titles where title_id=@title_id
order by 1
Select * from
The above UDF defines the schema of the table that is getting returned at the
definition itself. Now this can also get twisted by defining our own in-line
schema and use the same to return the results. Look at the sample below to get
an idea of how this works.
Create Function dbo.TitleTable
Return (Select title_id, title,
type From titles where title_id=@title_id)
Select * from
The syntax you observe above is more compact and has lesser lines of code with
the same functionality. The disadvantage is that since we are using this as
inline function we can restrict to using just a single select statement. and
not much of programming can be introduced in this setup.
Now the examples above have illustrated you the power of returning a table
datatype. Hence you can also use UDFs in conjunction with Inner Join clauses
and other features of using as a normal table is still supported.
The methods and usage above have specific scenarios. But there are tons of other
utilities that we can use UDFs. And this is exactly what we will discuss
in this section.
We can use UDFs in Views to be returned as columns. We might have used
UDFs in many of our view definitions unknowingly. Like the ISNULL and many
other system defined UDFs.
UDFs can also be used as Constraints or Default Values. This is an
interesting use and we may not use them often. Recently I had someone ask me
how can I populate the value of the identity column in another field without
using triggers. Since we cannot create two identity columns we had to work
around as below.
dbo.GetIdentity() RETURNS INT AS
CREATE TABLE vin_test
ID INT IDENTITY(1,1)
ID2 INT DEFAULT
INSERT INTO vin_test (colA)
SELECT * FROM vin_test
UDFs can be bound to Schema with the "with schemabinding" option availabe
during the definition. Refer to SQL Server BOL for the sytax specifics. 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.
I did briefly discuss of using non-determinitic functions in UDF is not
possible. But there have been tons of questions in newsgroups asking why they
cannot use getdate() in their UDF. Understand that getdate() is a built-in UDF
which is non-determinitic in nature. But there are workarounds for the same.
See the example below :
CREATE VIEW CurrentDate AS SELECT Date =
CREATE FUNCTION myFunction () RETURNS
DECLARE @myDateTime DATETIME
@myDateTime = Date FROM CurrentDate
While user defined functions can offer great convenience,
they can also sometimes hit performance. The problem with them is
that they use row-by-row processing instead of working as a set-based
operation. So if the result set of your query, which is using a user defined
function, is very small, then the performance hit will be small. But if the
result set is large, then performance would very well become a problem.
Generally speaking, if you are using a user defined function, you will want to
avoid using them with large result set.
Using UDFs in SQL Server can open multiple avenues to work and make
Transact-SQL an effective tool. We can perform great set based approaches
solutions using UDFs.