英文:
Is SQL Server able to reuse execution plans for functions when types differ in a meaningless way? Or is that only true of stored procedures?
问题
考虑以下关于用户定义的内联表值函数 foo 的查询，该函数具有一个参数 @bar varchar(MAX)：
DECLARE @Long nvarchar(500)
SELECT * FROM foo(@long)
DECLARE @Short varchar(5)
SELECT * FROM foo(@Short)
假设这些查询按顺序运行，并且这是第一次调用 foo。SQL Server 是否有任何机会使用来自第一次调用的缓存执行计划用于第二次调用？还是绝对保证不会使用？
我之所以提出这个问题，是因为我最近发现如果 foo 是一个存储过程（例如，使用 EXEC sp_foo @Short），那么 SQL Server 将强制将参数转换为与 @bar 的类型匹配，因此可能对两个查询使用相同的执行计划。我提出这个问题是因为我有印象认为对于表值函数不成立相同的情况。
英文:
Consider the following queries on a user-defined inline-table-valued function, foo, which has one parameter, @bar varchar(MAX):
DECLARE @Long nvarchar(500)
SELECT * FROM foo(@long)
DECLARE @Short varchar(5)
SELECT * FROM foo(@Short)
Suppose that these are run in sequence and that they are the first time foo has ever been called. Is there any chance at all that SQL server will use the cached execution plan from the first call for the second? Or is it absolutely guaranteed that it won't?
I've recently discovered that if foo were a stored procedure (e.g. using EXEC sp_foo @Short), then SQL Server will coerce the argument to match @bar's type and may therefore use the same execution plan for both queries. I'm asking this question because I've been put under the impression that the same is not true for table-valued functions.
答案1
得分: 2
绝对保证计划不会被分享。SQL Server使用二进制比较将查询与缓存的计划匹配。SQL计划甚至取决于空格，即使运行相同查询两次，但使用不同的空格，也会得到两个缓存的计划。对于参数化查询，参数定义也必须匹配。
英文:
Absolutely guaranteed that the plans won't be shared. SQL Server matches queries to cached plans using a binary comparison. SQL plans are even whitespace dependent, i.e. run a query twice, but with different whitespace and you will get two cached plans. For parameterized queries, the parameter definitions have to match as well.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论