Playing with user-defined functions

Microsoft’s SQL Server may be a big dog in the enterprise database market, but its conspicuous lack of user-defined functions (UDFs) has stacked up poorly against other leaders of that pack. Oracle, DB2, and even Access (Microsoft’s desktop database) have offered UDFs for years, so it’s about time Redmond added this useful functionality to SQL Server 2000.

With UDFs, you can more easily accommodate the unique requirements of a custom application. Most developers would list table variables and UDF calls from a SELECT statement as UDFs’ main claim to fame. But there’s much more: They increase functionality with less effort.

The big picture
All functions, whether built-in or user-defined, generally can be described as subroutines that return a value or complete a task. You pass information to the function in the form of arguments, and the function uses those passed values to evaluate an expression or complete a task. The result is an automated process that’s easy to rerun and share.

SQL Server always has offered built-in functions, such as GetDate, DateAdd, and ObjectName. Such built-in functions are useful, but you can’t alter their functionality in any way, and that’s why UDFs are so powerful and necessary.

UDFs allow you to add custom solutions for unique application-specific problems. Tasks can be as simple as calculating a value or as complex as defining and implementing table constraints. Technically speaking, SQL Server UDFs are subroutines of encapsulated Transact-SQL (T-SQL) logic that you can call into action via other T-SQL code to return a scalar (single) value or a table variable.

There are a number of reasons to use UDFs, but the main ones are as follows:

UDFs return table variables.

You can call a UDF from a SELECT statement.

You can join to a UDF.

Using table variables
SQL Server 2000 has a new Table data type that lets you declare local variables as type table. The table type is available only via a UDF.

Stored procedures can create and populate a table, but a table variable isn’t a physical table. If you’re familiar with Access and ActiveX Data Objects (ADO), you might compare a table variable to a Recordset object. Earlier versions of SQL Server offered temporary tables, but table variables perform faster than temporary tables, unless you surpass their cache level.

There are two types of temporary tables: private and global. Private tables are available only to the connection that created the table. In contrast, global temporary tables are available to all users, but they’re destroyed when the last user exits the table; there’s no way to control this behavior.

A table variable is available within the scope of the function. In other words, the table variable is available to the user executing the function while the function is in use. The life of the function controls the life of the table variable.

Calling and joining to UDFs
Executing a UDF from a SQL statement is a huge step forward for SQL Server. You can return a table variable and pass that data to a SELECT (or action keyword) statement as follows:

SELECT * FROM user.udf_name()
SELECT *, user.udf_name() FROM user.table

Similarly, you can join to a UDF, as follows, thereby increasing solution possibilities:
SELECT * FROM user.table WHERE user.table.column = user.udf_name.column

The new UDF vs. stored procedures
UDFs probably seem similar to stored procedures, but each offers a distinct functionality. Stored procedures are powerful and flexible tools, and certainly, they’re no less valuable than UDFs; they’re just different. Figure A lists a few differences.

Figure A

Stored procedures vs. UDFs

UDFs solve unique problems without too much effort, but they do have a few limitations. The biggest limitation is simply scope. Unlike built-in functions, which are available in every database, UDFs are available only to databases in which they’re created or copied. In addition, UDFs aren’t flexible enough to handle every function you throw at them. Specifically, UDFs can’t contain built-in, deterministic functions. To get a little more info on the basics of deterministic and nondeterministic functions, click here.

For example, using GetDate() within a UDF will return the following error:
Invalid use of 'getdate' within a function.

That’s because a nondeterministic function, such as GetDate, can return a different result using the same parameters. GetDate has two components, the actual date and the time. Each time you call it, the time component will have changed. Because each moment in time is unique, you can never return the same exact value.

For example, the following SQL can be executed in the Query Analyzer:
Select getdate()AS Date_One
Go
Select getdate() as Date_Two

Even if executed as part of a single file, two different values are returned:
2002-02-11 21:07:37.940
2002-02-11 21:07:37.950

Unfortunately, until now, memorizing the previous definitions was simpler than determining whether a function was deterministic or nondeterministic. Now, the OBJECTPROPERTY function makes this process simple. Use the function’s IsDeterministic property as follows:
SELECT OBJECTPROPERTY(OBJECT_ID('functionname'), 'IsDeterministic')

The function returns 0 (zero) when the function is nondeterministic and one (1) when the function is deterministic. Just remember that this particular property, IsDeterministic, only works with UDFs.

You can’t alter the determinism of a built-in function. In contrast, UDFs are deterministic if:

The UDF is schema-bound, which means the UDF is bound to an object (data source). (We’ll learn more about this in part two of this series.)

The UDF doesn’t call any extended stored procedures.

What's next?
UDFs are a powerful and welcome addition to SQL Server. They increase functionality while often reducing your development effort. In part two, we’ll continue our discussion of UDFs by showing you how to create one using CREATE FUNCTION and then reviewing several examples.