I have a database where i am using inline TVFs (table value functions) instead of views. For example, I might have two tables called [car model] and [car manufacturer] that I'm joining together inside the TVF [fnCarBrands].

These TVFs are then called by other TVFs to do further processing and reporting. So I might take my function [fnCarBrands] and join to the table [Purchase Year] to form a function [fnCarBrandHistory]. And so on for several layers of TVFs.

I could probably get the same functionality using views, since my inline TVFs are really just joins of tables and other TVFs.

How does the performance of inline TVFs written in this way compare with views?

  • Most likely you would see the same execution plans and the same performance.
    – A-K
    Aug 14, 2012 at 16:16
  • that's what i thought, but i was told that the TVF acts like parenthesis would in algebra -- it forces the DB engine to complete that query first before optimizing. Using views, i'm told, allows the optimizer to optimize the whole query as a unit.
    – FistOfFury
    Aug 14, 2012 at 16:35
  • Kind of a side note but any idea why TVFs were used instead of views? Just code-monkey vs data-monkey approach to the problem? Aug 14, 2012 at 22:13
  • @MarkStorey-Smith yes, the reason is as you say, code-monkey vs data-monkey approach.
    – FistOfFury
    Aug 15, 2012 at 15:40

3 Answers 3


The query optimizer treats an inline table valued function exactly like a view:

CREATE FUNCTION dbo.InlineUdf(@arg1 int)
    ... your query here ...

A multi-statement table-valued function is run more like a stored procedure. They typically have to be executed multiple times, rather than be folded into the main query:

CREATE FUNCTION dbo.MultiStatementUdf (@col1 int)
    id int primary key NOT NULL,
   DECLARE @var1 int
   set @var1 = 42

   INSERT @result
   SELECT ...
  • 1
    @Andomar my understanding is multi-statement TVFs are run like a black-box, the engine doesn't know what's in it and can't optimize the query. Do you have any articles you can reference that say views and inline TVFs are equivalent?
    – FistOfFury
    Aug 14, 2012 at 21:01

You'll need to create similar views to the functions and query each looking at the execution plan to see what's going on with each.

  • Exactly. Benchmarking, seeing for yourself - this is the only reliable way to learn.
    – A-K
    Aug 14, 2012 at 17:37
  • @mrdenny Is there no way to predict how the optimizer is going to treat each type of query? Surely there are some rules it follows.
    – FistOfFury
    Aug 14, 2012 at 21:04
  • 2
    @FistOfFury Yes there are rules, thousands upon thousands of them, complimented with hundreds of developer years of heuristic code. Which is why you'll have to test, or ask Microsoft for the optimiser source code. Aug 14, 2012 at 22:08
  • If you would better like to understand how the database engine determines what to do then you need to read up on SQL Server Internals, amazon.com/s/…
    – HLGEM
    Jul 22, 2016 at 15:54

Of course creating views that call other views is a performance killer too. Do not go down that route. Write the queries you need and don't use either TVFs or views if you want performance. It is the layering that is creating the problem, this is almost always a bad thing to do when querying a database and you can quickly end up hitting the limit of the number of tables you can reference too, especially since you often end up referencing the same tables in different layers. Further, while this seems as if it woudl be easier to maintain, it is not. Try debugging or adding a column due to a new requirement when the layer you need to fix is at the bottom.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.