UDFs

UDFs are subroutines developed by us (the users), to implement some common functionality. If there is a set of code that is common to two or more stored procedures, or if there is a business function, calculation, or data retrieval which will be used as either part of a JOIN or a filter or even as a value in a column when returning a rowset, then we can use a UDF to implement it.

For example, we can use a UDF to return a set of orders placed by a client. This UDF can form part of a JOIN condition. The data thus generated, could then be used for producing an invoice, or a delivery note, or outstanding delivery note, and so on; with just one routine we can easily cater to all these scenarios.

Important

Note that, UDFs cannot modify data; they are simply there to return information.

The earlier versions of SQL Server didn't have the concept of a UDF. Any functionality that was common and called from different stored procedures had to be implemented itself as a stored procedure. This approach wasn't always ideal; the value returned from such a stored procedure had to be placed into a variable and then the variable used in the T-SQL statement. Also, it was not possible to return a tabular set of data that could form part of a subset of data. With a UDF we can do all these, as well as other tasks, such as formatting the text of a column within a SELECT clause.

Some people argue that the UDFs of SQL Server 2000 are more powerful than in the other databases, as they can return a special set of data type, which looks just like a table of data within our database. In the Table Data Type section of this chapter we will discuss this data type in more detail.

In-Built Functions

SQL Server comes with many in-built functions, ranging from ones that provide us with system information to those performing some sort of complex calculation or variable manipulation. Some of the commonly used functions are:

• GETDATE()

  This function returns the current system date and time

• AVG()

  This is used to return an average value of a set of grouped rows

• COUNT()

  This function returns the number of rows that meet our join and filtering criteria

• ISNULL()

  This is used to test whether a column contains a null value or not

Unlike SQL Server's system stored procedures, we cannot inspect the T-SQL statements making up these in-built functions. However, we can easily find out the existing functions by using Query Analyzer; here, they are also grouped into relevant sections. This can be seen in the following screenshot:

Here, we can see the Object Browser of Query Analyzer. It not only lists the functions but also tells us what parameters need to be passed in and the function's return type. It saves us from having to refer to SQL Server Books Online for this information, thereby speeding up our development time.