Home Forums Article Discussions Article Discussions by Author Discuss Content Posted by Steve Jones The Basics of iTVFs RE: The Basics of iTVFs

  • January 10, 2019 at 2:56 pm

    #2018637

    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