Jeff Moden (3/1/2013)
TheSQLGuru (2/28/2013)
I would honestly expect (but not certain here) that a scalar UDF that just puts it's output into a variable would be more efficient than iTVF that returns one row one column table simply due to less overhead related to the output format (variable vs table).Then you've got to read the following.
1) on my laptop (powerful, with SQL 2012 SP1 CU2) sUDF and iTVF both run in EXACTLY the same time (143ms). I suppose the difference is a) your old machine and b) perhaps parallelism? My CTFP was set high enough so that the 2.x query cost of scanning that 1M row table and doing the calculation on the column didn't cause the iTVF to parallelize.
2) HOWEVER, your example was not what I was mentioning. I was speaking of the case of a SINGLE call to a sUDF to populate a variable that is used elsewhere and a SINGLE call to an iTVF to get same value into a temp object. Not calling each to make a calculation iteratively on a single large table.
I note that the two are not semantically equivalent and cannot be used identically later in the batch - I was just making an observation. Since it is confusing at best I will withdraw it to avoid further pursuit of a non-useful point. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service