Inline and Multi-statement table-valued functions

  • michael.leach2015

    SSCommitted

    Points: 1593

    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?
  • Grant Fritchey

    SSC Guru

    Points: 396617

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • michael.leach2015

    SSCommitted

    Points: 1593

    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.

  • Grant Fritchey

    SSC Guru

    Points: 396617

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

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