Thursday, June 9, 2011

Difference VIEW / Inline TVFs / Multistatement TVFs


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
(indexed) - Yes
Is Updatable - Yes
Can contain Multiple Statements
- No
Can have triggers
- Yes

Inline TVFs

Accepts Parameters               - Yes
Expanded out
by Optimiser - Yes
Can be Materialized
(indexed) - No
Is Updatable - Yes
Can contain Multiple Statements
- No
Can have triggers
- No

MultiStatement TVFs

Accepts Parameters               - Yes
Expanded out
by Optimiser - No
Can be Materialized
(indexed) - No
Is Updatable - No
Can contain Multiple Statements
- Yes
Reference : http://stackoverflow.com/questions/4960137/table-valued-function-vs-view
Can have triggers
- No

No comments: