Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Understanding User Defined Functions (UDF) in SQL Server 2000

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.

Simple 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 other datetrick article.

CREATE FUNCTION dbo.LastDayInMonth (@when DATETIME)
RETURNS Int
AS
BEGIN
            Declare @lastDate int
            SELECT @lastdate = DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@when),@when))))
            RETURN @lastDate
End

Hence now if I were to call this function as below I would get the last day of the current month.

Select dbo.LastDayInMonth(getdate())

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 datatype.

Note: We need to qualify the owner of the function when we reference the functon in our actual code. This is a mandatory requirement.

Recursive Functions

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 :

Create Function dbo.Factorial (@number int)
Returns int
As
BEGIN
            Declare @finalResult int            
            If @number < 2
                        Select @finalResult = @number
            Else
                        Select @finalResult = @number * dbo.factorial(@number - 1)
           
            Return @finalResult
END
GO
Select dbo.Factorial (8)

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.

Custom DataTypes

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.

sp_addtype ShortString, 'NVarchar(30)'
GO
Create Function LowerCase (@string ShortString)
Returns ShortString
as
BEGIN
            Return Lower(@string)
END
GO
Select dbo.LowerCase ('TesTing Our Function')

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.

Returning Table

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 (@title_id Varchar(6))
Returns @ReturnTable Table (title_id Varchar(6),
                                    title            Varchar(80),
                                    type            Char(12))
As
BEGIN
            Insert @ReturnTable
            Select title_id, title, type From titles where title_id=@title_id
            order by 1
            Return
End
GO
Select * from dbo.TitleTable('BU1032')

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 (@title_id Varchar(6))
Returns Table
As
Return (Select title_id, title, type From titles where title_id=@title_id)
GO
Select * from dbo.TitleTable('BU1032')

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.

Misc Usage

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.

CREATE FUNCTION dbo.GetIdentity() RETURNS INT AS
BEGIN
RETURN (IDENT_CURRENT('vin_test'))
END
GO
CREATE TABLE vin_test
(
ID INT IDENTITY(1,1) NOT NULL,
ID2 INT DEFAULT dbo.GetIdentity(),
colA VARCHAR(10)
)
GO
INSERT INTO vin_test (colA) VALUES ('Test')
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 = GETDATE()
GO
CREATE FUNCTION myFunction () RETURNS DATETIME AS
BEGIN
   DECLARE @myDateTime DATETIME
  
SELECT @myDateTime = Date FROM CurrentDate
   RETURN (@myDateTime)
END
GO
SELECT dbo.myFunction()
GO

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.

Conclusion

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.