SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Basics of iTVFs


The Basics of iTVFs

Author
Message
The Dixie Flatline
The Dixie Flatline
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52322 Visits: 6983
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.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
The Dixie Flatline
The Dixie Flatline
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52322 Visits: 6983
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)

Group: General Forum Members
Points: 976135 Visits: 49314
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Cade Roux
Cade Roux
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1286 Visits: 503
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.
The Dixie Flatline
The Dixie Flatline
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52322 Visits: 6983
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.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)SSC Guru (976K reputation)

Group: General Forum Members
Points: 976135 Visits: 49314
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
The Dixie Flatline
The Dixie Flatline
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52322 Visits: 6983
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.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Nico Dreyer
Nico Dreyer
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 3
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.
The Dixie Flatline
The Dixie Flatline
SSC Guru
SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)SSC Guru (52K reputation)

Group: General Forum Members
Points: 52322 Visits: 6983
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


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search