The Basics of iTVFs

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714708

    Comments posted to this topic are about the item The Basics of iTVFs

  • This was removed by the editor as SPAM

  • n.dreyer

    Grasshopper

    Points: 24

    Good article Steve
    What would the benefit then be using an iTVFs instead of a View? Or even better, an Indexed View?

    Thanks!
    Nico

  • Jeff Moden

    SSC Guru

    Points: 993924

    From the article...

    This was a major advance for T-SQL, but it didn't quite work out as well as Microsoft would have hoped.

    I'll beg to differ... it worked out just fine for M$ because they got to say they had functions in their product.  They didn't care about performance because, if they did, they'd have create iSF's (like they've recently done) instead of SFs and mTVFs.  I can't remember where I saw a comment in the M$ code but I do remember seeing reference to iSFs way back in 2005.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993924

    n.dreyer - Thursday, January 10, 2019 7:19 AM

    Good article Steve
    What would the benefit then be using an iTVFs instead of a View? Or even better, an Indexed View?

    Thanks!
    Nico

    1.  You don't suffer the duplication of data (Indexed Views materialize extra data even if it's aggregated).
    2.  You don't suffer the slowdowns that Indexed Views can cause when doing inserts or updates (I've seen indexed views cripple both)
    3.  Another advantage is that iTVFs are similar to views except you can pass parameters to them.  Think of it as a "parameterized view".  It doesn't always work out to be an advantage (Indexed Views can be damned fast because all of the aggregation work has already been done during inserts and updates).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993924

    Great article on the Basics of iTVFs, Steve.

    For those wondering about the performance of iTVFs that Steve mentioned, here's an article that discusses and demonstrates the performance advantage with code.  It also provides an example of how to make a function that returns a scalar value using an iTVF.
    How to Make Scalar UDFs Run Faster (SQL Spackle)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714708

    Derek-905092 - Thursday, January 10, 2019 5:16 AM

    So it looks to me like an iTVF without a parameter is essentially the same as a view. What criteria would you use to decide whether to use an iTVF or a view?

    iTVFs allow you to parameterize them, which a view doesn't. You can certainly add a WHERE clause, but that doesn't allow reusing of the code in the same way.

  • Larry Huisingh

    SSC Enthusiast

    Points: 154

    Steve Jones - SSC Editor - Thursday, January 10, 2019 9:46 AM

    Derek-905092 - Thursday, January 10, 2019 5:16 AM

    So it looks to me like an iTVF without a parameter is essentially the same as a view. What criteria would you use to decide whether to use an iTVF or a view?

    iTVFs allow you to parameterize them, which a view doesn't. You can certainly add a WHERE clause, but that doesn't allow reusing of the code in the same way.

    Doesn't using a view essentially reuse the code in the view?  Or is the code reuse you're referring to rest in the fact that you don't have to re-specify the where clause logic you would normally need for using a view.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714708

  • Larry Huisingh

    SSC Enthusiast

    Points: 154

    Steve Jones - SSC Editor - Thursday, January 10, 2019 12:21 PM

    WHERE logic.

    That makes sense.  If you repeatedly access views with the same WHERE clause structure then an iTVF might be a good solution.

  • The Dixie Flatline

    SSC Guru

    Points: 53197

    Good read, Steve.   Quick, clean, accurate, and on point.

    One thing worth mentioning when using APPLY with a table valued function is the difference between CROSS APPLY and OUTER APPLY.

    When CROSS apply is used, no row is returned from a query unless the function actually returns a row (even if the row only contains a NULL).   This is analogous to an INNER JOIN.

    When OUTER apply is used,  rows are returned even if the function itself returns nothing.   This is analogous to a LEFT JOIN.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline

    SSC Guru

    Points: 53197

    Jeff Moden - Thursday, January 10, 2019 8:32 AM

    n.dreyer - Thursday, January 10, 2019 7:19 AM

    Good article Steve
    What would the benefit then be using an iTVFs instead of a View? Or even better, an Indexed View?

    Thanks!
    Nico

    1.  You don't suffer the duplication of data (Indexed Views materialize extra data even if it's aggregated).
    2.  You don't suffer the slowdowns that Indexed Views can cause when doing inserts or updates (I've seen indexed views cripple both)
    3.  Another advantage is that iTVFs are similar to views except you can pass parameters to them.  Think of it as a "parameterized view".  It doesn't always work out to be an advantage (Indexed Views can be damned fast because all of the aggregation work has already been done during inserts and updates).

    Nico, 
    While I agree with Jeff's first two points about data duplication and transaction slowdown, our system has a couple of indexed views that are absolutely critical to searches that precede the transactions.    You can't have indexes underlying a table valued function the way you can an indexed view, BUT there is nothing that prevents you from writing a table valued function that uses the indexed view.    That gives you the best of both worlds for purposes of number 3 above.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Jeff Moden

    SSC Guru

    Points: 993924

    The Dixie Flatline - Thursday, January 10, 2019 2:56 PM

    Nico, 
    While I agree with Jeff's first two points about data duplication and transaction slowdown, our system has a couple of indexed views that are absolutely critical to searches that precede the transactions.    You can't have indexes underlying a table valued function the way you can an indexed view, BUT there is nothing that prevents you from writing a table valued function that uses the indexed view.    That gives you the best of both worlds for purposes of number 3 above.

    I guess I don't know what you mean.  iTVFs will use the indexes of underlying tables just fine and, if used properly, will come into play just fine for joins and the like.  I DO agree, however, that using Indexed Views is an incredible method for pre-aggregating data if you understand the effects they will have if you have rapidly changing table data that must be processed by Indexed Views.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Cade Roux

    Ten Centuries

    Points: 1316

    I think that the best thing about ITVF versus direct access to views is that you can enforce some parameters to be provided.  So even if views (indexed or not) do exist underneath, with an ITVF, you can require, for instance, a parameter to filter on date range or customer or whatever.

    If you limit access to only the ITVF and not the tables and views underneath, then users/developers/whatever will always have to provide those parameters - this can give a number of advantages:

    Limit accidental data leakage - no more forgotten WHERE clauses or weird inadvertant cross-joins when a table is not limited the way it should have been either in the join or in a where clause

    Limit long-running queries - no more selecting all 5 billion rows from a datawarehouse fact and associated dimensions when you should only be selecting a particular financial period or a particular date or similar limitiation

    Defensive coding and security in-depth - limiting scope of data accessible in a particular interface accessible by a particular user in a single call is yet one more way to limit potential misuse, whether due to bugs or vulnerabilities - coding defensively anticipates not how the code will be misused, but is general approach to limit the places where problems can happen by reducing surface area in general

    Focus thinking on actual use patterns - when parameters are always needed to be provided, this exposes a lot more of how the system works explicitly through the interface of the function instead of arbitrary column usage.

    Basically, codifying the expected access path and providing just that interface does the same thing you would expect it to do based on the general principle of defining interfaces in software - it controls and conforms access and gives you control of the surface area.

  • The Dixie Flatline

    SSC Guru

    Points: 53197

    Jeff Moden - Thursday, January 10, 2019 10:10 PM

    I guess I don't know what you mean.  iTVFs will use the indexes of underlying tables just fine and, if used properly, will come into play just fine for joins and the like.  I DO agree, however, that using Indexed Views is an incredible method for pre-aggregating data if you understand the effects they will have if you have rapidly changing table data that must be processed by Indexed Views.

    Jeff,  you can write an ITVF that reads from a view, including an indexed view, not just tables.   In fact, if the function references multiple tables and a suitable indexed view exists, the ITVF may use it automatically.     

    The point is that sometimes indexed views across multiple tables can save time and i/o by eliminating joins altogether, and that inline table functions can take advantage of indexed views.    ITVFs and indexed views aren't mutually exclusive.   They play nicely together, with the indexed view providing the structure and the ITVF providing control through parameters.      I hope this is a better explanation.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 15 posts - 1 through 15 (of 19 total)

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