Are views logically redundant?

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.

• 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.

StackExchange Database Administrators Q#336115, answer score: 17