sql-serversql-viewsql-function

Table-Valued Function(TVF) vs. View


What's the difference between table-valued functions and views? Is there something you can do with 1 that's hard or impossible to do with the other? Or does the difference lie in efficiency?


Solution

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

    Inline TVFs

    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    
    

    MultiStatement TVFs

    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    
    

    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.