User-Defined Functions (UDFs) are great, and have been part of SQL Server for a long time now. They come in two primary flavors: scalar and table-value. The first returns a single value, where as the second will return an entire result set. It’s not uncommon to want to reuse a block of SQL. Views are nice, but sometimes you want to be able to pass in parameters, which isn’t allowed with views.
UDFs do allow parameters, but in my experience they aren’t very efficient, especially if you are using the CROSS APPLY operator. When this happens, SQL usually must execute the stored procedure for each row in the result set. Even if your UDF is pretty light weight, this can cause a lot of extra overhead for the server. This is because a UDF can be a multi-statement function, and therefore SQL has to deal with it on a row-by-row basis, instead of working with an entire set of data at once (which is what SQL is optimized to do).
There is a way, however, to write a UDF such that SQL can continue to work with the entire set of data instead of executing the UDF row-by-row. This is known as an in-line UDF. A UDF is considered to be in-line if it is a table-valued UDF that contains a single SELECT statement. This means the UDF can NOT have any of the following:
- A single query (sub queries are allowed)
- No local variables
- No BEGIN/END statements
- Body of the UDF is a single RETURN statement
The simplest way to break it down is this: a normal UDF is like a block box. SQL puts values in, and values come back out. It doesn’t really know what’s going on. But, with an inline UDF, SQL no longer views it as a black box. Since it’s a single query, it can essentially “copy” and “paste” the contents from the UDF into the query that is calling it. This gives SQL a chance to optimize the overall query, and has the potential for coming up with a more efficient execution plan.
Here’s what a typical table-value UDF looks like:
CREATE FUNCTION [schema].[name] (@parameter1 type, @parameter2 type…)
RETURNS @result TABLE (Column1 type, Column2 type…)
BEGIN
[Multiple SQL Statements – inserting values into @result table variable]
RETURN
END
Here’s what an inline UDF looks like
CREATE FUNCTION [schema].[name] (@parameter1 type, @parameter2 type…)
RETURNS TABLE
AS
RETURN( [SQL Statement] )
Not all UDFs can be written as inline, but in my experience, a lot of them can be; it may require thinking outside the box a bit. Here are some tricks I’ve used to help me create an inline function:
- Instead of using IF statements, utilize the CASE statement
- If a function is a scalar value function, turn it into a table value function that returns a table with a single row that contains only one column
- Instead of creating local variables and setting their values, do the work inside of the query itself
- This one runs the risk of making your query harder to read, but if your goal is performance, then it may be worth it
