Do you want to speed up your table value functions? Sure you do. I suppose that was a silly question. Today I’m giving you a simple technique to help make them run faster. This isn’t new knowledge; but you may not have come across it yet.
The simplest table value function can be the same thing as a view. It is a query that returns a virtual, un-materialized, set of data. We already have views. Why do we need Table Value Functions? Table value functions differ from a view in that they can have procedural or looping logic, They can contain multiple queries, they may have input parameters, yet, they still return a set of data, just like a view.
You can use a Table Value Function just like any other SQL Server set object. You can join it, add a where clause on the results. Ordering, group by, etc. are all supported. It is a set.
The biggest value of a Table Value Function is that it has built in parameters. Those parameters are applied before the results are materialized, allowing you to specify when and how the queries are performed for the function.
Since Table Value Functions were introduced there have been two kinds. The first simply passes data through, applying any criteria you provide as input parameters.
CREATE FUNCTION dbo.fnGetOrdersForMonth(@Start DATETIME, @End DATETIME)
AS
RETURN
(
SELECT …
FROM ORDERS
WHERE OrderDate BETWEEN @Start AND @End
)
You could have made a view, and then filtered the results. By contrast, this function only returns the data meeting the criteria. If there are a lot of rows, or the query is joining a lot of tables, this implementation might be a better choice.
So, if your query can be resolved in an inline query, then your Table Value Function performance will most likely be the optimum.
Cheers,
Ben