SQL Server User-Defined Functions (UDFs)
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
User-defined functions are a long-awaited and widely demanded addition to SQL Server functionality. Previous articles in this series showed you the most useful system supplied functions. When you can't find a built-in function that meets your needs, you can write your own. This article will introduce you to various types of user-defined functions (UDFs), show you their syntax, and discuss situations in which each one is useful.
UDF Types
UDFs come in three flavors: scalar, in-line and multi-statement functions.
Scalar UDFs
Scalar UDFs return a single value. They are similar to built-in functions such as DB_NAME(), GETDATE(), or USER_ID(), which return a single string, date, or integer. The value returned by a scalar UDF can be based on the parameters passed, although UDFs don't have to accept parameters.
Scalar UDFs can return any scalar system-supplied data type, except TIMESTAMP. You cannot return values with a user-defined data type from scalar UDFs. If you want to return a value with a user-defined data type, you must specify the underlying system-supplied data type instead.
Scalar UDFs also prohibit returning values with non-scalar data types such as TABLE or CURSOR.
Scalar functions are excellent for lookups, such as returning the greatest quantity of sales for a particular title. Alternatively, scalar functions can be used successfully for calculations: Determining a number of business days between two dates would be an excellent task for a UDF, for instance.
In-line UDFs
In-line UDFs return a single row or multiple rows and can contain a single SELECT statement. Because in-line UDFs are limited to a single SELECT, they can't contain much logic. They can be effective, however, for lookups that return multiple values, such as the top five best-selling books with title, author, and publication date.
Multi-statement UDFs
Finally, the multi-statement UDFs can contain any number of statements that populate the table variable to be returned. Notice that although you can use INSERT, UPDATE, and DELETE statements against the table variable being returned, a function cannot modify data in permanent tables. Multi-statement UDFs come in handy when you need to return a set of rows, but you can't enclose the logic for getting this rowset in a single SELECT statement.
Reasons for using a multi-statement UDF, as opposed to an in-line UDF, can vary. In some cases, the large number of joins against big tables (having more than 100,000 rows) warrants poor performance for a single SELECT statement. If this is the case, you can bring the initial set of rows into the table variable with fewer joins. Then you could join the table variable to the rest of the tables in a second SELECT statement. In other instances, you might need to perform conditional logic and apply UPDATE or DELETE statements to the rows in the table variable prior to returning the rowset to the user.