1

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.

IamIC
  • 17,747
  • 20
  • 91
  • 154
  • What is this added convenience that you speak of with regard to implementing your solution as a MTVF? There may be scope for an additional question here whereby we can propose an alternative, more desirable solution. – John Sansom Nov 24 '10 at 11:22
  • The added convenience is the ability to put result paging and paramaterized filtering into the UDF, get the results as a table of IDs, and use a JOIN on this result to get the desired columns from the original table. One clean outer-query. – IamIC Nov 24 '10 at 13:22
  • I believe the solution is to figure out how to convert the MTVF into an ITVF. – IamIC Nov 24 '10 at 13:30

1 Answers1

1

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'.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Thanks for your answer. Just so I understand this, let's say I have a multi-statement TVF with a simple "INSERT INTO @Results (ColX) SELECT ColX FROM TABLE" statement. Are you saying SQL Server will execute this once for each row? That part doesn't make sense to me. – IamIC Nov 24 '10 at 04:06
  • http://stackoverflow.com/questions/4254814/sql-server-table-valued-functions-vs-stored-procedures – gbn Nov 24 '10 at 05:05
  • 2
    @IanC: an inline TVF can be optimized in certain ways that are not accessible to multi-statement TVFs. Eg. it can be transformed into a merge join and moved up the query tree. Or the projection list (fields) of the TVF result can be pruned early if not referenced and a narrow index can be chosen. With a multi-statement, these optimization paths are closed. Whether the TVF will be evaluated once, multiple times, or one time per row, depends on a multitude of factors. – Remus Rusanu Nov 24 '10 at 17:08