OPTIMIZE 选项可以在多语句表函数中使用吗？
我有 SQL Server 2008 Express，因此我没有所有工具来查看幕后发生的情况。有人向我建议，由于多语句表函数是一个“黑匣子”，因此 SQL Server 可能会忽略以下内容：
OPTION (OPTIMIZE FOR (@JobID UNKNOWN, @Status UNKNOWN, @ResellerID UNKNOWN))
有人有这方面的证据吗？
我知道如果我使用存储过程，这不会成为问题。然而，使用多语句表函数为我需要做的事情提供了很多便利。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用 Express，您可以使用与任何其他版本相同的信息，只是没有 GUI 工具来
mangle显示它。例如，执行计划仍然可以在 DMV 中使用，例如sys.dm_exec_query_plan。我不确定您问的问题是什么，但内联表函数确实是比多语句表函数更好的选择。优化器可以看到 TVF 的作用，并可以在整个查询的上下文中对其进行正确优化，也许可以消除对函数的不必要的调用或选择有助于减少整个查询的总体聚合时间的访问路径（索引）。使用多语句 TVF，计划每次都被迫有效地调用和评估函数（即对于每个候选行）并查看结果是什么。当您的朋友说多语句 TVF 是“黑匣子”时，这可能就是您的意思。
With Express you have the same information at your disposal as with any other version, you just don't have the GUI tools to
mangledisplay it. For instance execution plans are still available in the DMVs likesys.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'.