Multi-Statement Table Valued Functions in SQL Server - Part 32

We have discussed about scalar functions in Part 29 and Inline Table Valued functions in Part 30. In this video session, we will discuss about Multi-Statement Table Valued functions.







Multi statement table valued functions are very similar to Inline Table valued functions, with a few differences. Let's look at an example, and then note the differences. 

Employees Table:


Let's write an Inline and multi-statement Table Valued functions that can return the output shown below.



Inline Table Valued function(ILTVF):
Create Function fn_ILTVF_GetEmployees()
Returns Table
as
Return (Select Id, Name, Cast(DateOfBirth as Date) as DOB
       From tblEmployees)


Multi-statement Table Valued function(MSTVF):
Create Function fn_MSTVF_GetEmployees()
Returns @Table Table (Id int, Name nvarchar(20), DOB Date)
as
Begin
Insert into @Table
Select Id, Name, Cast(DateOfBirth as Date)
From tblEmployees

Return
End

Calling the Inline Table Valued Function:
Select * from fn_ILTVF_GetEmployees()

Calling the Multi-statement Table Valued Function:
Select * from fn_MSTVF_GetEmployees()

Now let's understand the differences between Inline Table Valued functions and Multi-statement Table Valued functions
1. In an Inline Table Valued function, the RETURNS clause cannot contain the structure of the table, the function returns. Where as, with the multi-statement table valued function, we specify the structure of the table that gets returned
2. Inline Table Valued function cannot have BEGIN and END block, where as the multi-statement function can have.
3. Inline Table valued functions are better for performance, than multi-statement table valued functions. If the given task, can be achieved using an inline table valued function, always prefer to use them, over multi-statement table valued functions.
4. It's possible to update the underlying table, using an inline table valued function, but not possible using multi-statement table valued function.

Updating the underlying table using inline table valued function: 
This query will change Sam to Sam1, in the underlying table tblEmployees. When you try do the same thing with the multi-statement table valued function, you will get an error stating 'Object 'fn_MSTVF_GetEmployees' cannot be modified.'
Update fn_ILTVF_GetEmployees() set Name='Sam1' Where Id = 1

Reason for improved performance of an inline table valued function:
Internally, SQL Server treats an inline table valued function much like it would a view and treats a multi-statement table valued function similar to how it would a stored procedure.

7 comments:

  1. hi venkat,
    is there any difference between procedure and view?performance wise which is best?

    ReplyDelete
    Replies
    1. In addition to this, We can pass parameters to Procedure but we can not pass parameters to views

      Delete
    2. Use stored procedure over function for better Cache Plan.

      Delete
    3. calling view with where condition is same as calling procedure with parameters

      Delete
  2. Replied by Chiranjeet Ghosh : There is a huge difference between procedure and view
    You can perform DML operations in procedures but the same thinf is noy possible with views

    ReplyDelete
  3. can i call stored procedure in to the function block

    ReplyDelete

It would be great if you can help share these free resources