SQL Server view or table-valued function?

Question

Anyone have a good decisioning tree for deciding when to use a view and when to use a table-valued function in SQL Server?

Solution 1

Although any view can almost trivially be converted to an inline table-valued function, the converse is not true.

If the construct needs to be parametrized, then use an inline table-valued function. Inline table-value functions are basically parametrized views in terms of the optimizer being able to combine them with views and push things around. Multi-statement table-valued functions are not at all like inline table-valued functions.

If you cannot do it with an inline table-valued function, use a multi-statement table-valued function.

Solution 2

There's certain things you can't do in a view (such as table variables, intermediate results before you return your result-set, etc.) ... if you don't need those, view, if you do, sproc/udf :-)