Thank you for all the replies,
So to summarize, I should have a look at my databases and identify the most expensive views. Irrespective if they are indexed or not. These will more likely be the ones where the source tables have a lot of insert / update /delete activity on them. Then do a test run to see if an iTVF is not a better and cleaner solution and substantially less expensive on resources. Also, if you do not trust your developers and limit them a bit more, sell them the idea of using parameters rather than a view with a where clause 😉
I must admit, what I have found with views in the past is exactly as Jeff says, in some instances it uses unnecessary space where a "stored parameterized query" aka iTVF would have been a better solution. Then @The Dixie Flatline's approach might even be a good migration from an indexed view to an iTVF to eliminate major optimization path changes as a first iteration. Then one by one start removing the view from the iTVF's code and replace it with the original JOINs that made up the view, using only the required columns for that function's expected result and test the resource utilization.
I do have some new projects lined up and I am definitely adding iTVF to my list for implementation. I am 100% convinced that there will be instances where an iTVF makes more sense. Honestly, don't quote me on this, but I am considering aligning this with my C# function models and controllers as I see a synergy between iTVFs and the functions in the code calling it. Interesting.
Nico,
Couple of points to clarify, from my posts:
(1) ITVFs are treated exactly like views in that they are just predefined queries incorporated into the query execution plan. With parameters of course.
(2) ITVFs cannot be indexed, they can only take advantage of existing indexes.
(3) We did NOT migrate from an indexed view, our ITVFs just take advantage of the index structure underlying the indexed view. Kill the index and you kill performance.
Best of luck with your changes.
Bob
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills