Design T-SQL table-valued and scalar functions
May include but is not limited to: inline table-valued functions vs. views, multi-statement table-valued functions, determinism
Another feature of SQL Server which is very useful, but I’m not using it daily – User Defined Functions (UDFs). A UDF is like any other function. It takes parameters, works on them, and then return some results. There are three types of UDFs: inline table-valued functions, multistatement table-valued functions and scalar functions.
Let’s start with scalar UDFs, because they can be described very easily. They are functions that accepts zero or more parameters, and returns a single scalar value at the end. There are some built-in scalar functions in SQL Server, like ABS or SUM or COUNT, so you can have a good grasp on what I’m talking about. UDFs have a more rigid syntax than stored procedures, so here’s a brief one:
CREATE FUNCTION schema.name (@parameter1 = 1)
WITH RETURNS NULL ON NULL INPUT
body of UDF, do some calculations here
RETURN value of type specified
WITH RETURNS NULL ON NULL INPUT isn’t mandatory, but if your results depends on the input parameters, you can save some CPU cycles. A bit more info on UDF parameters. If you create a UDF without parameters, you must still include the empty parentheses (like in the GETDATE() function). In the previous example, we gave the default value 1 to @parameter1. If you’d like to use the default value, you must include the DEFAULT word in its place, you cannot omit it like you can in stored procedures.
Notice that you can implement recursion in scalar UDFs, but you can use a CTE inside them, too. Now UDFs can be quite complicated, for example in his book I’m using for preparing this exam, Pro SQL Server 2008 Programmer’s Guide, Michael Coles creates a UDF which uses a complex algorithm to match similar sounding names.
The next type of UDFs to work with is multistatement table-valued function. It has roughly the same syntax as a scalar UDF, but immediately after the RETURNS keyword, you declare a variable of type table, and return that one. And you don’t write anything after the RETURN keyword in the end of the UDF.
An inline table-valued UDF is a bit more interesting, because you don’t have to define a table at the beginning of it. You simply write RETURN table, to indicate that you want an inline TVF. Like a view, it’s built from a single SELECT statement, but it can accept parameters. And one more thing- there’s no “body” and BEGIN, END keywords.
One more aspect of UDFs is determinism. We say a UDF is deterministic when they always return the same result when called with the same input. Like SUBSTRING or ISNULL. Certain features demand the use of deterministic UDFs, like a calculated column participating in a primary key.
Nondeterministic functions are functions that can return different values called with the same inputs. Like GETDATE or RAND.