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?

Solution 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.

Solution 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...

Solution 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.

Solution 4

Nobody seems to have mentioned this aspect.

You can't have Update statements in an inline function but you can write Update statements against them just as though they were an updatable view.

Solution 5

One big difference is that a function can take parameters whereas a VIEW cannot.

I tend to favour VIEWs, being a Standard and therefore portable implementation. I use functions when the equivalent VIEW would be meaningless without a WHERE clause.

For example, I have a function that queries a relatively large valid-time state table ('history' table). If this was a VIEW and you tried to query it without a WHERE clause you'd get a whole lot of fairly data (eventually!) Using a function establishes a contract that if you want the data then you must supply a customer ID, a start date and an end date and the function is how I establish this contact. Why not a stored proc? Well, I expect a user to want to JOIN the resultset to further data (tables, VIEWs, functions, etc) and a function is IMO the best way of doing this rather then, say, requiring the user to write the resultset to a temporary table.

View more solutions