February 9, 2012 at 10:30 pm
When would you use a temporary table instead of an inline table-valued function?
Is an inline table-valued function the same as a multi-statement TVF that only had one table-valued SELECT statement in it? If so, why do you think Microsoft or whoever comes up with technical SQL terms called it like that? It would be easier if they were called single-SELECT statement table-valued functions and multi-SELECT statement table-valued functions.
February 10, 2012 at 7:06 am
Really depends on what's going on with the query. An inline table function is sort of like a permament CTE in that they're both just queries you're incorporating into your other query. A temp table is semi-permanent storage mechanism, not a query. So, it's down to, will a query work, or do you need that semi-permanent storage? I don't think there's a hard rule for that.
As to the differences between inline & multi-statement, no, it's not just that one has a single statement. The multi-statement TVF defines a structure into which it loads information. That structure is a table variable internally. So you're getting a semi-permanent storage, but to a place that has no statistics. That's a good thing & a bad thing (more often a bad thing, IMO). It just works differently than the inline TVF which is just a query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2012 at 3:22 am
Rowles (2/9/2012)
When would you use a temporary table instead of an inline table-valued function?Is an inline table-valued function the same as a multi-statement TVF that only had one table-valued SELECT statement in it? If so, why do you think Microsoft or whoever comes up with technical SQL terms called it like that? It would be easier if they were called single-SELECT statement table-valued functions and multi-SELECT statement table-valued functions.
To add to what Grant has already stated, the "inline Table Valued Function" (iTVF) operates more like a view than a function in that the code of an iTVF is "absorbed" and becomes a part of the overall execution plan as if the code were "inline" with the rest of the code. The code for a scalar UDF and mTVF are not.
As to your questions on naming, once you understand how these particular things work, I think you'll understand the names for them.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply