In SQL Server, a scalar UDF, or scalar user-defined function, is a user-defined function that returns a single value.

This is in contrast to a table-valued function, which returns a result set in the form of a table.

User-defined functions can be written as either T-SQL UDFs or CLR (Common Language Runtime) UDFs. CLR enables you to create UDFs in managed code using any .NET Framework programming language. These functions are then accessible to T-SQL or other managed code.

Example 1 – Basic Scalar UDF

Here’s an example the code used to create a basic T-SQL scalar UDF.

CREATE FUNCTION dbo.ufn_discountPrice( 
    @price DECIMAL(12,2), 
    @discount DECIMAL(12,2) 
    ) 
RETURNS DECIMAL (12,2)
AS
BEGIN
  RETURN @price * (1 - @discount);
END;

This scalar UDF accepts two parameters; @price and @discount. These are passed in to the function as arguments whenever the function is invoked. The function takes the value of those arguments, performs a calculation using those values, then returns the resulting value. In this case, the discounted price is returned.

Example 2 – Invoke the UDF

Once the UDF has been created, it can then be invoked within T-SQL code any time you need it.

Here’s an example of invoking the UDF:

SELECT dbo.ufn_discountPrice(100, .2) AS Result;

Result

+----------+
| Result   |
|----------|
| 80.00    |
+----------+

You can also use scalar UDFs in computed columns. This can be particularly handy if you need the computed column to access data in another table.

Function Options

When you create a scalar UDF, there are various options that you can apply to the function.

For example you can specify whether or not it should be schema bound. You can specify that it should be encrypted. You can also specify a EXECUTE AS clause to specify the security context under which the user-defined function is executed.

See the Microsoft documentation for a detailed list of options you can provide when creating or altering a user-defined function.

Parameter Data Type

For T-SQL functions, all data types, including CLR user-defined types and user-defined table types, are allowed except timestamp and the nonscalar cursor and table types.

For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, user-defined table types, timestamp data types, and the nonscalar cursor and table types.

Return Data Type

The return data type for T-SQL scalar UDFs can be any data type except timestamp and the nonscalar cursor and table types.

CLR functions can use any data type except text, ntext, image, timestamp, and the nonscalar cursor and table types.

Both T-SQL and CLR functions can have a CLR user-defined type for its return value.