Types of Functions

Officially, SQL Server supports three types of user-defined functions:

  • Scalar functions

  • Inline table-valued functions

  • Multistatement table-valued functions

The next few sections take an in-depth look at the differences between the function types and how and where you can use them.

Scalar Functions

Scalar functions are like the built-in functions provided with SQL Server. They return a single scalar value that can be used anywhere a constant expression can be used in a query, as you saw in the previous example of the getdateonly() function.

Scalar functions typically take one or more arguments and return a value of a specified datatype. All functions must return a result using the RETURN statement. The value to be returned can be contained in a local variable defined within the function, or the value can be computed in the return statement. The following two functions are variations of a function that returns the average price for a specified type of book from the titles table:

CREATE FUNCTION AverageBookPrice(@booktype varchar(12) = '%') 
    DECLARE @avg money
    SELECT @avg = avg(price)
    FROM titles
    WHERE type like @booktype

    RETURN @avg

CREATE FUNCTION AverageBookPrice2(@booktype varchar(12) = '%')

    RETURN ( SELECT avg(price)
             FROM titles
             WHERE type like @booktype)

As mentioned earlier in this chapter, the scalar functions can be used anywhere a constant expression can be used. For example, SQL Server doesn't allow aggregate functions in a WHERE clause unless they are contained in a subquery. The AvgBookPrice() function lets you compare against the average price without having to use a subquery:

select title_id, type, price from titles 
where price > dbo.AverageBookPrice('popular_comp')

title_id type         price
-------- ------------ ---------------------
PC1035   popular_comp               22.9500
PS1372   psychology                 21.5900

When invoking a user-defined scalar function, you must include the owner name. The owner name is typically the name of the user that created the function. If you omit the owner name, you will see the following error, even if the function is owned by dbo and is the only instance of such a function in the database:

select AverageBookPrice('popular_comp') 

Server: Msg 195, Level 15, State 10, Line 1
'AverageBookPrice' is not a recognized function name.

You can return the value from a user-defined scalar function into a local variable in two ways. You can assign the result to a local variable using the SET statement or an assignment select, or you can use the EXEC statement. The following commands are functionally equivalent:

declare @avg1 money, 
        @avg2 money,
        @avg3 money
select @avg1 = dbo.AverageBookPrice('popular_comp')
set @avg2 = dbo.AverageBookPrice('popular_comp')
exec @avg3 = dbo.AverageBookPrice 'popular_comp'
select @avg1 as avg1, @avg2 as avg2, @avg3 as avg3

Warning: Null value is eliminated by an aggregate or other SET operation.
avg1                  avg2                  avg3
--------------------- --------------------- ---------------------
              21.4750               21.4750               21.4750

Notice, however, that when using a function in an EXEC statement, it is invoked similarly to a stored procedure and no parentheses are used around the function parameters. Also, when invoked in the EXEC statement, the function generated the warning message Warning: Null value is eliminated by an aggregate or other SET operation. This warning wasn't generated when invoked in the SET or SELECT statement. To avoid confusion, stick to using the EXEC statement for stored procedures and invoke scalar functions as you would normally invoke a SQL Server built-in function.

Table-Valued Functions

Table-valued user-defined functions return a rowset instead of a single scalar value. You can invoke a table-valued function in the FROM clause of a SELECT statement, just as you would a table or view. In some situations, a table-valued function can almost be thought of as a view that accepts parameters so the result set is determined dynamically. A table-valued function specifies the keyword TABLE in its RETURNS clause. Table-valued functions are of two types: inline and multistatement. What the two types return and the way they are invoked are the same. The only real difference is the way the function is written to return the rowset. The next couple of sections look at each of these types of table-valued functions.

Inline Table-Valued Functions

An inline table-valued function specifies only the TABLE keyword in the RETURNS clause without table definition information. The code inside the function is a single RETURN statement that invokes a SELECT statement.

For example, you could create an inline table-valued function that returns a rowset of all book types and the average price for each type where the average price exceeds the value passed into the function:

CREATE FUNCTION AveragePricebyType (@price money = 0.0) 

    RETURN ( SELECT type, avg(price) as avg_price
             FROM titles
             group by type
             having avg(price) > @price)

You can invoke the function by referencing it in a FROM clause as you would for a table or view:

select * from AveragePricebyType (15.00) 

type         avg_price
------------ ---------------------
popular_comp               21.4750
trad_cook                  15.9633

Notice that when you invoke a table-valued function, the owner name does not have to be specified as it does with a user-defined scalar function.

Multistatement Table-Valued Functions

Multistatement table-valued functions differ from inline functions in two major ways:

  • The RETURNS clause specifies a table variable and its definition.

  • The body of the function contains multiple statements, at least one of which populates the table variable with data values.

Before getting into an example of a multistatement table-valued function, this chapter will briefly discuss table variables. (For more information on table variables, see Chapter 26.)

The table datatype is defined in the RETURNS clause. The syntax to define the table variable is similar to the CREATE TABLE syntax. Note that the name of the variable comes before the TABLE keyword:

RETURNS @variable TABLE ( column definition | table_constraint [, ...] ) 

The scope of the table variable is limited to the function in which it is defined. While the contents of the table variable are returned as the function result, the table variable itself cannot be accessed or referenced outside the function itself.

Within the function, table variables can be treated like a regular table. Any SELECT, INSERT, UPDATE, or DELETE statement can be performed on the rows in a table variable, except for SELECT INTO and an INSERT where the result set to be inserted is from a stored procedure, as in the following example:

INSERT INTO table_variable EXEC stored_procedure 

The following example defines the inline table-valued function AveragePricebyType() as a multistatement table-valued function:

CREATE FUNCTION AveragePricebyType2 (@price money = 0.0) 
RETURNS @table table (type varchar(12) null, avg_price money null)
    insert @table
       SELECT type, avg(price) as avg_price
             FROM titles
             group by type
             having avg(price) > @price

Notice the main differences between this version and the inline version. In the multistatement version, you had to define the structure of the table rowset you are returning and also had to include the BEGIN and END statements as wrappers around the multiple statements that the function can contain. Other than that, both functions are invoked the same way and return the same rowset:

select * from AveragePricebyType2 (15.00) 

type         avg_price
------------ ---------------------
popular_comp               21.4750
trad_cook                  15.9633

Why use multistatement table-valued functions instead of inline table-valued functions? The main reason is if you need to perform further inserts, updates, or deletes on the contents of the table variable before returning a result set, or if you need to perform additional processing on the input parameters to the function before invoking the query to populate the table variable.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features