表值函数 (TVF) 与视图 [英] Table-Valued Function(TVF) vs. View

问题描述

表值函数和视图有什么区别?有什么你可以用 1 做而另一个很难或不可能做的事情吗?还是效率的区别?

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?

推荐答案

无参数内联 TVF 和非物化视图非常相似.下面是一些让人想到的功能差异.

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

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  

内联 TVF

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    

多语句 TVF

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    

在运行时视图和内联 TVF 都被内联，并以类似于派生表或 CTE 的方式进行处理.它们很可能不会被整体评估(甚至在某些情况下根本不会被评估)或者可能在其他情况下被多次评估.多语句 TVF 将始终被评估并存储在返回表类型(基本上是一个表变量)中

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)

偶尔能够与针对视图的等效参数化查询相比，直接参数化内联 TVF 可以产生更好的执行计划.

这篇关于表值函数 (TVF) 与视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！