Difference Between a Inline Function and a View

Table-Valued Function(TVF) vs. View

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 in advance   - Yes (through indexed views)
Is Updatable                     - Yes 
Can contain Multiple Statements  - No
Can have triggers                - Yes
Can use side-effecting operator  - Yes  

Inline TVFs

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    

MultiStatement TVFs

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.

What is the difference between Stored Functions and Views in DB?

View:
A view is a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables. View returns a table.

Stored procedure: A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

stored procedures returns Output parameters,return codes (which are always an integer value),
a result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure,a global cursor that can be referenced outside the stored procedure.

key benefits of stored procedure are Precompiled execution, reduced client/server traffic,efficient reuse of code, programming abstraction and enhanced security controls.

Update:

A stored function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section

Multi-statement Table Valued Function vs Inline Table Valued Function

In researching Matt's comment, I have revised my original statement. He is correct, there will be a difference in performance between an inline table valued function (ITVF) and a multi-statement table valued function (MSTVF) even if they both simply execute a SELECT statement. SQL Server will treat an ITVF somewhat like a VIEW in that it will calculate an execution plan using the latest statistics on the tables in question. A MSTVF is equivalent to stuffing the entire contents of your SELECT statement into a table variable and then joining to that. Thus, the compiler cannot use any table statistics on the tables in the MSTVF. So, all things being equal, (which they rarely are), the ITVF will perform better than the MSTVF. In my tests, the performance difference in completion time was negligible however from a statistics standpoint, it was noticeable.

In your case, the two functions are not functionally equivalent. The MSTV function does an extra query each time it is called and, most importantly, filters on the customer id. In a large query, the optimizer would not be able to take advantage of other types of joins as it would need to call the function for each customerId passed. However, if you re-wrote your MSTV function like so:

CREATE FUNCTION MyNS.GetLastShipped()
RETURNS @CustomerOrder TABLE
    (
    SaleOrderID    INT         NOT NULL,
    CustomerID      INT         NOT NULL,
    OrderDate       DATETIME    NOT NULL,
    OrderQty        INT         NOT NULL
    )
AS
BEGIN
    INSERT @CustomerOrder
    SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
    FROM Sales.SalesOrderHeader a 
        INNER JOIN Sales.SalesOrderHeader b
            ON a.SalesOrderID = b.SalesOrderID
        INNER JOIN Production.Product c 
            ON b.ProductID = c.ProductID
    WHERE a.OrderDate = (
                        Select Max(SH1.OrderDate)
                        FROM Sales.SalesOrderHeader As SH1
                        WHERE SH1.CustomerID = A.CustomerId
                        )
    RETURN
END
GO

In a query, the optimizer would be able to call that function once and build a better execution plan but it still would not be better than an equivalent, non-parameterized ITVS or a VIEW.

ITVFs should be preferred over a MSTVFs when feasible because the datatypes, nullability and collation from the columns in the table whereas you declare those properties in a multi-statement table valued function and, importantly, you will get better execution plans from the ITVF. In my experience, I have not found many circumstances where an ITVF was a better option than a VIEW but mileage may vary.

Thanks to Matt.

Addition

Since I saw this come up recently, here is an excellent analysis done by Wayne Sheffield comparing the performance difference between Inline Table Valued functions and Multi-Statement functions.

His original blog post.

Copy on SQL Server Central

Inline table-valued function vs Stored Procedure (SQL Server)

Using a multi-statement table valued function is similar to using a proc from plan caching and cached plan reuse perspective.. Using an inline table valued function is similar to using a view from the plan cache and plan reuse perspective(reuse only happen is exact same statement is used. ie same parameters).
Considering the same you should use a multi-statement table valued function.

Inline function versus normal select

Think of an ITVF as a view you can pass parameters to. It is essentially included in your script that references it as if it was plain old SQL and then executed. This is why they perform better than multi statement table valued functions, which have to be executed as a seperate statement.

Due to this, in your examples the statement:

select *
from InlineFun (1)

Is essentially passed to the query engine as:

select *
from (select *
      from Number
      where n = 1
     ) as a

So to actually answer your question, the scope of the function is the same as the scope of the statement that calls it.

---

Related Topics