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

A function performs a task, or many tasks. A view retrieves data via a query. What ever fits in that query is what you are limited too. In a function I can update, select, create table variables, delete some data, send an email, interact with a CLR that I create, etc. Way more powerful than a lowly view!

Answer 2

A view is a "view" of data that is returned from a query, almost a pseudo-table. A function returns a value/table usually derived from querying the data. You can run any sql statement in a function provided the function eventually returns a value/table.

Answer 3

A function allows you to pass in parameters to create a more specific view. Lets say you wanted to have grab customers based on state. A function would allow you to pass in the state you are looking for and give you all the customers by that state. A view can't do that.

Answer 4

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.

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

Answer 6

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 7

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 8

Answering your question about updates in a function (msdn):

The only changes that can be made by the statements in the function are changes to objects local to the function, such as local cursors or variables. Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.

Answer 9

Relevant link:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-with-cross-apply.aspx

Answer 10

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.