Table-Valued Function(TVF) vs. View

A parameterless inline TVF and a non materialized View are very similar. A few functional differences that spring to mind are below.

Views

Accepts Parameters               - NoExpanded out by Optimiser        - YesCan be Materialized in advance   - Yes (through indexed views)Is Updatable                     - Yes Can contain Multiple Statements  - NoCan have triggers                - YesCan use side-effecting operator  - Yes  

Inline TVFs

Accepts Parameters               - YesExpanded out by Optimiser        - YesCan be Materialized in advance   - NoIs Updatable                     - YesCan contain Multiple Statements  - NoCan have triggers                - NoCan use side-effecting operator  - No    

MultiStatement TVFs

Accepts Parameters               - YesExpanded out by Optimiser        - NoCan be Materialized in advance   - NoIs Updatable                     - NoCan contain Multiple Statements  - YesCan have triggers                - NoCan use side-effecting operator  - No    

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) or may be evaluated multiple times in others. Multistatement TVFs will always be evaluated and stored in the return table type (basically a table variable)

Occasionally the ability to parameterise inline TVFs directly can lead to a better execution plan than the equivalent parameterised query against a view.

I generally have a rule of thumb when it comes to deciding whether to convert my SELECT to a VIEW or a TVF.

Does the view take longer than 2 seconds to finish and does it have more than 10,000 records? If YES, turn it into a TVF. If not, leave it alone.

Of course the rule is purely based on performance.

With a TVF, I can use a CROSS APPLY, for example to treat it as a table, but passing a specific value, such as the primary key.

WHERE ID = xxx, where 'xxx' is the value I pass in the SELECT.

Performance is way faster!

If I had a view of the TVF, I would have to allow the view to bring back over 2 million rows just to return less than 1% of that in my SELECTs.

Something to think about.

I have found that joins with MultiStatement TVFs perform much better than Views when a PK is specified on the function's return table.

CREATE FUNCTION [FORMREQS].[fnGetFormsStatus] ()RETURNS/* Create a PK using two of the columns */@Indexed TABLE (    [OrgID] [char](8) NOT NULL,    [PkgID] [int] NOT NULL,    [FormID] varchar(5) NOT NULL,    PRIMARY KEY CLUSTERED(OrgID, PkgID) )ASBEGININSERT @Indexed SELECT OrgID, PkgID, FormID FROM FormsTableRETURNEND