Menu
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?
226
A table-valued function in SQL Server is a user-defined function that accepts zero or more parameters and returns a table variable. Moreover, it also allows users to query the result of the function. On the other hand, a view is just a SQL statement with a name.
A view is a database object that allows generating a logical subset of data from one or more tables. A table is a database object or an entity that stores the data of a database. The view depends on the table.
Summary: Views and Functions almost serve the same purpose. But the major difference is that Function can accept parameters, where as Views cannot. And also the output of the User Defined Function can be directly used in the SELECT clause, whereas you cannot do it with a View.
A view is a virtual table. A view consists of rows and columns just like a table. The difference between a view and a table is that views are definitions built on top of other tables (or views), and do not hold data themselves. If data is changing in the underlying table, the same change is reflected in the view.
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 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 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.
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
