January 10, 2019 at 12:04 am
Comments posted to this topic are about the item The Basics of iTVFs
January 10, 2019 at 5:16 am
This was removed by the editor as SPAM
January 10, 2019 at 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
January 10, 2019 at 8: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.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2019 at 8:32 am
n.dreyer - Thursday, January 10, 2019 7:19 AMGood 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).
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2019 at 8: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)
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2019 at 9:46 am
Derek-905092 - Thursday, January 10, 2019 5:16 AMSo 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.
January 10, 2019 at 11:40 am
Steve Jones - SSC Editor - Thursday, January 10, 2019 9:46 AMDerek-905092 - Thursday, January 10, 2019 5:16 AMSo 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.
January 10, 2019 at 12:21 pm
WHERE logic.
January 10, 2019 at 12:34 pm
Steve Jones - SSC Editor - Thursday, January 10, 2019 12:21 PMWHERE logic.
That makes sense. If you repeatedly access views with the same WHERE clause structure then an iTVF might be a good solution.
January 10, 2019 at 2:51 pm
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
January 10, 2019 at 2:56 pm
Jeff Moden - Thursday, January 10, 2019 8:32 AMn.dreyer - Thursday, January 10, 2019 7:19 AMGood article Steve
What would the benefit then be using an iTVFs instead of a View? Or even better, an Indexed View?Thanks!
Nico1. 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
January 10, 2019 at 10:10 pm
The Dixie Flatline - Thursday, January 10, 2019 2:56 PMNico,
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
Change is inevitable... Change for the better is not.
January 11, 2019 at 9: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.
January 11, 2019 at 8:31 pm
Jeff Moden - Thursday, January 10, 2019 10:10 PMI 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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply