RE: The Basics of iTVFs – SQLServerCentral

SSC Guru

Points: 1003894

January 10, 2019 at 10:10 pm

#2018661

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)