SQLServerCentral

The Basics of iTVFs


https://www.sqlservercentral.com/Forums/Topic2015947.aspx

By Steve Jones - SSC Editor - Wednesday, January 9, 2019 5:04 PM

Comments posted to this topic are about the item The Basics of iTVFs
By Derek-905092 - Wednesday, January 9, 2019 10:16 PM

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?
By n.dreyer - Thursday, January 10, 2019 12: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
By Jeff Moden - Thursday, January 10, 2019 1:25 AM

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.
By Jeff Moden - Thursday, January 10, 2019 1: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).
By Jeff Moden - Thursday, January 10, 2019 1:37 AM

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)
By Steve Jones - SSC Editor - Thursday, January 10, 2019 2: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.
By Larry Huisingh - Thursday, January 10, 2019 4:40 AM

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.
By Steve Jones - SSC Editor - Thursday, January 10, 2019 5:21 AM

WHERE logic.
By Larry Huisingh - Thursday, January 10, 2019 5:34 AM

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.
By The Dixie Flatline - Thursday, January 10, 2019 7:51 AM

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.
By The Dixie Flatline - Thursday, January 10, 2019 7:56 AM

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.
By Jeff Moden - Thursday, January 10, 2019 3:10 PM

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.
By Cade Roux - Friday, January 11, 2019 2:01 AM

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

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.
By Jeff Moden - Saturday, January 12, 2019 12:13 AM

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?
By The Dixie Flatline - Saturday, January 12, 2019 3:54 PM

Jeff Moden - Saturday, January 12, 2019 7:13 AM
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?

No, but it does on my desktop, which is running SQL 2016. I changed my text to say a function may use it because at first I thought an ITVF would have to reference the view explicitly. I guess it shouldn't be surprising: an indexed view is just another table structure. I'll test it at work next week on 2012 and get back to you.

One thing to be clear on. All of our indexed views involve multiple tables. They aren't aggregations. We pay the price to avoid some expensive joins in big searches but fortunately the rows in the tables involved are fairly static once they're inserted.
By n.dreyer - Sunday, January 13, 2019 7:24 PM

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.
By The Dixie Flatline - Monday, January 14, 2019 2:56 AM

n.dreyer - Monday, January 14, 2019 2:24 AM
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