sp_executesql是参数部分的长度定义发生变化能否重用执行计划

去年techtalk的时候有同事问了我一个问题：使用sp_executesql是参数部分的长度定义发生变化能否重用执行计划。当时回答不能，但当时并没有给出示例，其实自己也没有尝试过。今天把实验代码贴出来

 

准备工作

create table dbo.ta(id varchar(10) ,c char(10))
go
insert dbo.ta 
select ROW_NUMBER() over(order by c1.name) id,'x' from sys.columns c1 cross join sys.columns c2
go
create index index1 on dbo.ta(id)
go
dbcc freeproccache
go

 

接下来要用select *from ta where id='1'的方式执行两次，看看是否重用了存储过程

 

dbcc freeproccache
go
DECLARE @value varchar(1);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'select * from dbo.ta 
       WHERE id = @id';
SET @ParmDefinition = N'@id varchar(1)';
/* Execute the string with the first parameter value. */
SET @value = '1';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @id = @value;
go
DECLARE @value varchar(2);
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'select * from dbo.ta 
       WHERE id = @id';
SET @ParmDefinition = N'@id varchar(2)';
/* Execute the string with the first parameter value. */
SET @value = '10';
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @id = @value;
go

 

查看执行计划

 

SELECT  execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where st.text  not like '%dm_exec_sql_text%'

 

 

执行结果如下：

 

execution_count      statement_text
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                    select * from dbo.ta
       WHERE id = @id
1                    select * from dbo.ta
       WHERE id = @id

(2 行受影响)

 

 

说明长度发生变化无法重用计划