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.
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.bcp.exe cannot query from a TVF without an actual SELECT query.SELECT query, and modifications may not work.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.
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 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.
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.)
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)
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).
