I have SQL Server 2008 Express, so I don't have all the tools to see what is happening under the hood. Someone suggested to me that since a multi-statement table function is a "black box", that SQL Server may ignore the following:
OPTION (OPTIMIZE FOR (@JobID UNKNOWN, @Status UNKNOWN, @ResellerID UNKNOWN))
Does anyone have proof of this either way?
I know that if I were using a stored procedure, this wouldn't be an issue. However, using a multi-statement table function offers a lot of convenience for what I need to do.
With Express you have the same information at your disposal as with any other version, you just don't have the GUI tools to mangle display it. For instance execution plans are still available in the DMVs like sys.dm_exec_query_plan.
I'm not sure what the question you ask is, but is true that inline table functions are a much better choice than multi-statement table functions. The optimizer can see what the TVF does and can properly optimize it in the context of the entire query, perhaps eliminating unnecessary calls to the function or choosing an acces path (an index) that helps reduce the overall, aggregate time of the entire query. With a multi-statement TVF the plan is forced to effectively call and evaluate the function each time (ie. for each candidate row) and see what the result is. This is what probably your friend means when it says that multi-statement TVF are 'black-box'.
