4

Earlier today, I realised that I had made a very stupid mistake. Rather than write a view, I wrote a parameterless inline table-valued function. This got me thinking: Interface aside, is there any difference at all between the two? As far as I know, they are logically the same and perform identically.

So, to rephrase, what do views offer that parameterless inline table-valued functions don't? All that came to mind was indexed views, but I've never seen anyone actually use those.

7

2 Answers 2

17

Both views and inline TVFs are updatable, and can be used interchangeably almost everywhere.

Specifically for SQL Server, the following features are supported by views that are not available for inline TVFs:

  • WITH CHECK OPTION, a little-used option that prevents updates to rows in an updatable view which would result in that row being excluded from the view.
  • Indexed views, which allow a view to be materialized and indexed.
    TVFs can still use such views (even implicitly) but you cannot index a TVF directly.
  • bcp.exe cannot query from a TVF without an actual SELECT query.
  • Most GUI clients will not show a TVF as browsable in a grid view, even if it is parameterless. You would need to write a SELECT query, and modifications may not work.
  • Some clients understand the column definitions better when coming from a view (such as when using sp_describe_first_result_set).

TVFs can obviously use parameters, which views cannot. This can often make it far more efficient than a similar view would be, for example when pushing predicates into window functions. They can also be APPLYd on a row-by-row basis, instead of using joins, which can make them far more flexible.

Should you use one over the other? I think a parameterless TVF doesn't make much sense. You can't index it, so you'd need to repeat the code if you wanted to do so. It's also more idiomatic to use a view, and they are more obvious to users. Intellisense in some IDEs also doesn't work well with functions.


Do not use multi-statement TVFs, or scalar UDFs, unless you absolutely can't avoid it, as both are often a major performance issue.

6
  • As Steve mentions, views can be written to; if it's true that inline TVFs don't allow that, that's a much more significant difference from what you've mentioned here.
    – IMSoP
    Feb 22 at 12:28
  • @IMSoP But that is factually incorrect, at least in SQL Server as you can see dbfiddle.uk/hcWDs6oN which is why I mention on the very first line that they are both updatable subject to the normal Updatable Views rules Feb 22 at 12:48
  • Ah, sorry, I missed that first line, and it's not what I would have expected. Apparently it's true even if they have parameters: dbfiddle.uk/Pu_tfgKn Interesting to know! :)
    – IMSoP
    Feb 22 at 13:55
  • The major performance issues of functions have been mitigated since SQL Server 2019, since it can now in-line most UDF, there are some exception
    – Bob Klimes
    Feb 22 at 14:23
  • 2
    Scalar UDFs weren't the main question, and there are so many caveats to inlining that I would still avoid learn.microsoft.com/en-us/sql/relational-databases/… Also many bugs have come up, more will probably still appear. Feb 22 at 14:29
8

Background

Concepts in mainstream SQL database engines have evolved gradually over decades to fulfil the most prominent needs at the time, and there have also been a number of industrial standards which define the basic functionality, which are difficult to revise once established.

The result is that it's not always easy to fathom the rationale for the design.

The concept of a "view" has, to my knowledge, existed from early in the history of SQL, possibly from the very outset - either way, it is certainly one of the most ancient concepts. Meanwhile, table-valued functions, with the possibility of arguments supplied, is a much later addition to SQL, and I'm not sure how standard it is.

Views

Views perform a number of functions.

A main one of which is to provide a layer of indirection for tables so that the underlying storage schema can be more easily adapted without breaking existing code. Syntactically, SQL does not differentiate between reference to a view and reference to a table, and a view can (under certain constraints) be the target of an insert, update, or delete, exactly as a table can be.

Another purpose of views is to act as a securable object which is distinct from the underlying table, so that filtered or summarised data can be provided, and access to it controlled, independently of the base table.

Some engines also provide "indexed" or "materialised" views, in which the results of a query are prepared and stored by the engine separately from the occasion on which the results are called for, and may be kept fresh by the database engine which analyses the lineage of the results and responds to changes in the originating data by updating the stored results automatically.

A purpose for which I think views were not originally designed, was SQL code modularity purely as a convenience for the programmer. The SQL language was once considerably more limited in capability than it now is in most engines, and so was the performance of the machinery that hosted a database engine, and there would usually not have been a great deal of intrinsic complexity in the SQL code of a view itself. Nowadays however, views may be pressed into service like this, in encapsulating a piece of SQL code which is common to many queries, and complex in a way that makes duplication unreasonable.

Table-valued functions

The purpose of table-valued functions is solely to get data out, whereas that is not the sole purpose of views, which can also accommodate putting data in.

In SQL Server which I'm most familiar with, there's also a distinction between "inline" functions and "multi-statement" functions.

Only the case of an inline and parameterless function, closely resembles a view. A multi-statement function resembles a stored procedure more than a view, but unlike a procedure can participate in a query in a similar way as a table or view can.

(EDIT: Per a comment from @Charlieface, and as a qualification to the point about "getting data out", although this point is true of multi-statement functions, it seems that inline TVFs can in fact be "updatable" like views can be.)

Why the overlap?

You can see already that the main purposes which views fulfil does not fit squarely with that of parameterised functions, even in the degenerate parameterless case.

I'm not sure offhand whether there is any case where an inline and parameterless function is not fully interchangable with a view. I suspect this case of overlap is allowed purely for completeness of the "function" concept, and because it arises naturally without additional complexity or implementation effort.

A multi-statement function can however achieve things a view clearly could not (or at least, might not without relatively long, garbled, and non-performant code, and might not without many of the more recent capabilities of the SQL language)

Summary

In summary, views and functions have arisen at different times in the evolution of SQL, and mainly cover different purposes. The small amount of overlap that exists does not remotely mean either fully subsumes all the purposes of the other.

The essential difference is that a view stands in for a whole table and has many table-like qualities (including syntactic equivalence to a table, read/write functionality, and the ability to assign triggers), whereas a function stands in for a reusable element of a Select query only.

In general, I make fair use of views for many different purposes, but I rarely use functions (as they are a much more heavyweight concept in SQL than "functions", or methods, would be in other programming languages).

7
  • 1
    This reads like a collection of relevant statements placed in seemingly random order. It reminds me of college students trying to produce an essay by cut and pasting lecture notes together. Subsets of this answer are useful and relevant to the question, but the excess of context is useless at best and confusing at worst. I am sincerely doubting that the source of this is human.
    – J. Mini
    Feb 22 at 8:00
  • 4
    @J.Mini, yes I thought after that it was a bit of a ramble. But alas, it is human-generated, albeit in the middle of the night after a tiring day.
    – Steve
    Feb 22 at 8:07
  • 1
    To be honest, I found it quite clear, but maybe that's because I tend to ramble myself... I've proposed an edit which just adds some headings and emphasis to highlight the sections which were already there.
    – IMSoP
    Feb 22 at 12:23
  • @IMSoP, the heading structure you have edited in has improved the wall of text dramatically. Thank you.
    – Steve
    Feb 22 at 12:27
  • 1
    Inline TVFs are updatable as well dbfiddle.uk/hcWDs6oN Feb 22 at 12:49

Your Answer

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

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