RE: The Basics of iTVFs – SQLServerCentral

SSC Guru

Points: 1003863

January 12, 2019 at 7:13 am

#2018750

The Dixie Flatline - Friday, January 11, 2019 8:31 PM
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.

Oh, I get all that. Still, an indexed view is a materialization of data and, unless the view is doing data reduction through aggregates, it's not much better than using coving indexes because that's what an indexed view kind of is. As with all else in SQL Server, "It Depends".

The only thing that I don't agree with is the part where a function may automatically pick to use an indexed view instead of the tables that have been cited in the code. I've heard of that "feature" before (and the DTA even has such an option built into it) and I suppose that optimizer could chose to use an index from a materialized view, but I've never seen anyone be able to prove it, especially where the view provides materialized aggregation. Do you have an example or a URL that has such an example handy?

--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)