Post reply

Inline and Multi-statement table-valued functions

  • February 12, 2020 at 11:47 pm

    #3725417

    When you run an inline or a multi-statement table-valued function, either type of function will return a table.

    1. Is that table a temporary table?
    2. Does that table have local scope or global scope?
    3. If #2 is yes, how can you control if the table returned will have local scope or global scope?

  • February 13, 2020 at 1:00 pm

    #3725585

    1. Nope. The multi-statement UDF is effectively a table variable. It lives within the context of the query it runs within. The in-line table function is just a parameterized view, a query. It doesn't materialize in a meaningful way at all. Look to the execution plans to see how these are resolved. Use what's called the "estimated plan" to see the plan for the multi-statement UDF as well as how it gets resolved within the query that calls it.
    2. Local, very local. Not even to the batch. Just to the statement. Unlike a real table variable or temporary table, you can't even use these in multiple statements. Each statement they're used in is a new call to the function.
    3. You can't.

    By the way, Multi-Statement User-Defined Table-Valued Functions have a nickname: Pure Evil.

    I would avoid them like the plague. There is almost literally nothing that can be done with them that can't be done with other objects within SQL Server and with superior performance by orders of magnitude.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 18, 2020 at 12:35 am

    #3726625

    Grant Fritchey wrote:

     

      <li style="list-style-type: none;">

    1. Nope. The multi-statement UDF is effectively a table variable. It lives within the context of the query it runs within. The in-line table function is just a parameterized view, a query. It doesn't materialize in a meaningful way at all. Look to the execution plans to see how these are resolved. Use what's called the "estimated plan" to see the plan for the multi-statement UDF as well as how it gets resolved within the query that calls it.

     

      <li style="list-style-type: none;">

    1. Local, very local. Not even to the batch. Just to the statement. Unlike a real table variable or temporary table, you can't even use these in multiple statements. Each statement they're used in is a new call to the function.

     

      <li style="list-style-type: none;">

    1. You can't.

     

    By the way, Multi-Statement User-Defined Table-Valued Functions have a nickname: Pure Evil.

    I would avoid them like the plague. There is almost literally nothing that can be done with them that can't be done with other objects within SQL Server and with superior performance by orders of magnitude.

     

    Thank you for your input.  I heard that the multi-statement functions are not very efficient.

  • February 18, 2020 at 12:35 pm

    #3726704

    michael.leach2015 wrote:

    Thank you for your input.  I heard that the multi-statement functions are not very efficient.

    That doesn't even remotely cover just how horrific performance gets with these things. I would just take them off the plate for possible development objects. You'll live a much happier life without them.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply