StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A parameterless inline TVF and a non materialized View are very similar. A few functional differences that spring to mind are below.</p> <h2>Views</h2> <pre><code>Accepts Parameters - No Expanded out by Optimiser - Yes Can be Materialized in advance - Yes (through indexed views) Is Updatable - Yes Can contain Multiple Statements - No Can have triggers - Yes Can use side-effecting operator - Yes </code></pre> <h2>Inline TVFs</h2> <pre><code>Accepts Parameters - Yes Expanded out by Optimiser - Yes Can be Materialized in advance - No Is Updatable - Yes Can contain Multiple Statements - No Can have triggers - No Can use side-effecting operator - No </code></pre> <h2>MultiStatement TVFs</h2> <pre><code>Accepts Parameters - Yes Expanded out by Optimiser - No Can be Materialized in advance - No Is Updatable - No Can contain Multiple Statements - Yes Can have triggers - No Can use side-effecting operator - No </code></pre> <p>At runtime Views and Inline TVFs are both inlined and treated similarly to derived tables or CTEs. They may well not be evaluated in their entirety (or even at all in some cases) <a href="https://stackoverflow.com/a/26205087/73226">or may be evaluated multiple times in others</a>. Multistatement TVFs will always be evaluated and stored in the return table type (basically a table variable)</p> <p><a href="http://sqlperformance.com/2013/03/t-sql-queries/the-problem-with-window-functions-and-views" rel="noreferrer">Occasionally the ability to parameterise inline TVFs directly can lead to a better execution plan than the equivalent parameterised query against a view.</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload