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


The Basics of iTVFs


The Basics of iTVFs

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)

Group: Administrators
Points: 632803 Visits: 21355
Comments posted to this topic are about the item The Basics of iTVFs

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Derek-905092
Derek-905092
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 71
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?
Nico Dreyer
Nico Dreyer
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 3
Good article Steve
What would the benefit then be using an iTVFs instead of a View? Or even better, an Indexed View?

Thanks!
Nico
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)

Group: General Forum Members
Points: 912996 Visits: 48751
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

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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)

Group: General Forum Members
Points: 912996 Visits: 48751
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).

--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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)SSC Guru (912K reputation)

Group: General Forum Members
Points: 912996 Visits: 48751
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

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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)

Group: Administrators
Points: 632803 Visits: 21355
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Larry Huisingh
Larry Huisingh
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 118
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.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)SSC Guru (632K reputation)

Group: Administrators
Points: 632803 Visits: 21355
WHERE logic.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Larry Huisingh
Larry Huisingh
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

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