Difference between a inline function and a view

Question

I am a newbie in using functions and it appears to me that an inline function is very similar to a view. Am I correct?

Also, can I have UPDATE statements within a function?

Answer 1

After reading many of the answers here, I'd like to note that there is a big difference between an inline table-valued function and any other kind of function (scalar or multi-line TVF).

An inline TVF is simply a parameterized view. It can be expanded and optimized away just like a view. It is not required to materialize anything before "returning results" or anything like that (although, unfortunately, the syntax has a RETURN.

A big benefit I've found of an inline TVF over a view is that it does force required parameterization whereas with a view, you have to assume that the caller will appropriately join or restrict the usage of the view.

For example, we have many large fact tables in DW with a typical Kimball star model. I have a view on a fact table-centered model, which called without any restriction, will return hundreds of millions of rows. By using an inline TVF with appropriate parameterization, users are unable to accidentally ask for all the rows. Performance is largely indistinguishable between the two.

Answer 2

No difference. They are both expanded/unnested into the containing query.

Note: indexed views are considered differently but still may be expanded, and multi-valued table functions are black boxes to the containing query.

Tony Rogerson: VIEWS - they offer no optimisation benefits; they are simply inline macros - use sparingly

Adam Machanic: Scalar functions, inlining, and performance: An entertaining title for a boring post

Related SO question: Does query plan optimizer works well with joined/filtered table-valued functions?

Scary DBA (at the end)

Finally, writes to tables are not allowed in functions

Edit, after Eric Z Beard's comment and downvote...

The question and answers (not just mine) are not about scalar udfs. "Inline" means "inline table valued functions". Very different concepts...

Answer 3

Update: Looks like I missed the "inline" part. However, I'm leaving the answer here in case someone wants to read about difference between VIEWs and regular functions.

If you only have a function that does SELECT and output the data, then they are similar. However, even then, they are not the same because VIEWs can be optimized by the engine. For example, if you run SELECT * FROM view1 WHERE x = 10; and you have index on table field that maps to X, then it will be used. On the other hand, function builds a result set prior to searching, so you would have to move WHERE inside it - however, this is not easy because you might have many columns and you cannot ORDER BY all of those in the same select statement.

Therefore, if you compare views and functions for the same task of giving a "view" over data, then VIEWs are a better choice.

BUT, functions can do much more. You can do multiple queries without needing to join tables with JOINS or UNIONs. You can do some complex calculations with the results, run additional queries and output data to the user. Functions are more like stored procedures that are able to return datasets, then they are like views.