SQL Server view or table-valued function?

sql-server tsql view user-defined-functions
Striker picture Striker · May 13, 2011 · Viewed 24k times · Source

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

Answer

Cade Roux picture Cade Roux · May 13, 2011

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.

Related questions

Create a view with ORDER BY clause

I'm trying to create a view with an ORDER BY clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error: Msg 102, Level 15, State 1, Procedure TopUsers, …

Read More
Cannot create index on view 'View_Table_Name' because the view is not schema bound

I am using Views in my stored Procedure(SQL-Server). For Improving Performance, I have tried to created INDEX of that View. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER VIEW VW_Table_Name AS SELECT Col1,Col2,…

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More