A very tough TSQL question, er... I mean CHALLENGE

[NuclearNed]

Originally posted by: oog
can you rewrite the function to not be gadget_key specific? it sounds like you're going to join on this column anyway, so you can remove it as a parameter from the function, let it create the full table as the result and then join it to reduce the resultset.

As stated earlier in the thread, creating the full table would take hours because of the complexity of the computations, as well as the fact that there are 350000+ gadget records in existence. So this is not an option.

 

[torpid]

OK, I assume the function returns multiple calculated values in the row back, right? What about splitting up each calculated value into sub-functions that return a single value, and then you can keep your current function that returns multiple values and just call the sub functions from the SQL? I don't know the nature of the calculations enough to say whether this is really feasible or not. If not, it seems like a stored proc or similar is your only reasonable solution.

 

[NuclearNed]

Originally posted by: torpid
OK, I assume the function returns multiple calculated values in the row back, right? What about splitting up each calculated value into sub-functions that return a single value, and then you can keep your current function that returns multiple values and just call the sub functions from the SQL? I don't know the nature of the calculations enough to say whether this is really feasible or not. If not, it seems like a stored proc or similar is your only reasonable solution.

Without going into all the reasons, this isn't really feasible. I think you may be right about using the stored procedure or whatever.

 

[NuclearNed]

Reply I got from someone over at the Microsoft SQL Server forums:[/quote]
Hi,

This syntax is neither supported in Yukon nor Shiloh. I believe the problem is that the output rowset cannot be materialized until the function is evaluated, and yet, the function cannot be evaluated until the output rowset is materialized.

In Yukon we've introduced a new relational operator called CROSS APPLY that you could use in scenarios like this. The LHS of CROSS APPLY is a table source and the RHS is a table-valued function. The formal input parameters of the function can be bound to actual column values materialized in the LHS rowset. In other words, for each row of the LHS, evaluate the function on the RHS and JOIN the results to the LHS, resulting in >=1 row in the ultimate output rowset. In essense, it solves the problem described above by assigning a formal and well-defined evaluation strategy to the LHS and RHS of the CROSS APPLY.

It would look like this:

select * from gadget cross apply udf_gadget_values(gadget_key)

Moreover, in Yukon, we've changed the parser to allow function input parameters to bind to correlated subqueries in FROM clause and in the projection list. The examples below illustrate:

-- Yukon : works
-- Shiloh: !works
select * from gadget where exists
(select * from udf_gadget_values(gadget.gadget_key))

-- Yukon : works
-- Shiloh: !works
select *
, (select gadget_desc
from dbo.udf_gadget_values(gadget.gadget_key))
as function_value
from gadget


Regards,
Clifford Dibble
Program Manager, SQL Server